Excel – How to extract excel data from multiple worksheets and put into one sheet

databasemicrosoft excelmicrosoft-excel-2010worksheet-function

In a workbook I have 7 sheets(Totals and then Mon to Sat),I want to extract rows which have the word "CHEQ" in its cell (this is a dropdown list with two options-CHEQ/PAID)from all sheets. On my front sheet I used this formula:

=IF(ROWS(A$13:A13)>$C$10,"",INDEX(Monday!A$3:A$62,SMALL(IF(Monday[Paid]=$A$10,ROW(Monday[Paid])-ROW(Monday!$I$3)+1),ROWS(A$13:A13))))

This formula works fine for one worksheet (eg. Monday) but is it possible to show the extracted rows from all 6 sheets on the front page? I only have Excel NOT Access. These are the 12 headers on row A12

Col Name     Cod  House Car Date  Discount 2nd Paid  Extra Letter Posted

The exported data appears like this (this just an example):

Col Name     Cod  House Car Date  Discount 2nd Paid  Extra Letter Posted
12  Robbs    1244 Ren         11/10 10%  5   CHEQ  0     0    No
15  Jones    7784 Ren         12/10 15%  1   CHEQ  0     0    No
18  Doese    1184       Ren   12/11 12%  1   CHEQ  0     0    No

Any ideas on what to do to this formula? I am using Excel 2010.

Best Answer

I'd probably do this by putting a button on the totals page connected to a VBA macro.

In the macro, I would apply the appropriate filter to each details page. Then copy the filtered results to the totals page. You will need, to track the number of rows pasted and set a variable to keep the next free row straight.

Then you can click the button each time you want to update the totals.

Related Solutions

Excel – How to filter for unique values using the most recent Date in a specific column

Select your entire sheet. Use the Sort function and choose the date column you want. Then order it from newest to oldest, press OK. Keep the entire sheet selected, now to to Remove Duplicates, unselect all columns and then choose the column you need unique values from. This method will put all newest dates at the top when it removes the duplicates, you're left with unique values that match to the top most (recent dates).

Excel – In Excel I need to find data matches between two arrays – one horizontal and one vertical

YMMV, but View -> Macros, add a macro. Try this (change the cell references as necessary):

Dim data, reference As Range

Set reference = Worksheets("Sheet2").Range("A1", "A42")
Set data = Worksheets("Sheet1").Range("B2", "F6")

For Each dataCell In data
    For Each referenceCell In reference
        If dataCell.Value = referenceCell.Value Then
                Worksheets("Sheet1").Cells(dataCell.Row, 1).Value = dataCell.Value
        End If
    Next
Next

[Edit: If this works, you can speed it up a bit by stopping searching a row when you've found a match. (Assuming only one possible match per row). e.g.:

Sub newtest()
    Dim data, reference As Range
    Dim skipsome As Boolean
    skipsome = False

    Set reference = Worksheets("Sheet2").Range("A1", "A7")

    Set data = Worksheets("Sheet1").Range("B2", "F6")
    For Each dataCell In data
        For Each referenceCell In reference
            If dataCell.Value = referenceCell.Value Then
                    Worksheets("Sheet1").Cells(dataCell.Row, 1).Value = dataCell.Value
                    skipsome = True
                    Exit For
            End If

            If skipsome = True Then
                skipsome = False
                Exit For
            End If
        Next
    Next
End Sub

Just on your 5 row test data this knocks the cell comparison tests down from 175 to 132.] [Edit2: Make the code work]

Best Answer

Related Solutions

Excel – How to filter for unique values using the most recent Date in a specific column

Excel – In Excel I need to find data matches between two arrays – one horizontal and one vertical

Related Question