Find all matches and consolidate them

[BKGLTS]

Hello,

I have a spreadsheet with multiple sheets.
Sheet1 Column A has alpha numeric values in it like M1, M2, M3, etc. along with empty cells between them. There may be duplicates but that doesn't matter.
I need a formula that I can put on Sheet2 that will generate a consolidated list of what is on Sheet1 based on certain criteria.

For Example:

SHEET1
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]M1
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]Twigs[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]M1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Berries[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]M2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Twigs[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

​

SHEET2
I need the formula to look in Column A on Sheet1 and find all the rows that have a value starting with the letter "M", and then return the values Sheet 1 Columns B & C, but consolidated as shown here.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Twigs[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Berries[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you,
B

 

[DanteAmor]

For that you can use a pivot table:

 

[BKGLTS]

For that you can use a pivot table:

DanteAmor,

This sounds fantastic.
Unfortunately, I have never messed with pivot tables and do not know how to create them and utilize them properly.
Can you provide a few instructional steps on how to set this up please?

Thank you!
B

 

[DanteAmor]

Of course.

1. Select the cells you want to create a PivotTable from. (ex A1 to C7)


2. Tab menú Insert > PivotTable.


3. In Table/Range, verify the cell range.


4. In chosee where > select Existing worksheet.
5. In Location > select cell E1


6. Select OK.


Building out your PivotTable


7. To add a field to your PivotTable, select the field name checkbox in the PivotTables Fields pane.


Following my example.

8. Drag field AN to FILTERS area.
9. Drag field St to ROWS area.
10. Drag field VAL to VALUES area.

To filter data
11. Select filter icon (cell F1)
12. Within the Search field type the characters m* > Ok

To sum data
13. On the right side, at the bottom, VALUES Area, click on the VAL field> select Settings > select Sum > Ok

With the pivot table you can make other groupings.

 

[BKGLTS]

Not exactly working as I need, will post shortly what issues I am running into. Thank you!

 

[BKGLTS]

Dante,

I need the pivot table to update automatically whenever there is a change made to the data in the data range and it needs to automatically include any new entries without having to redo the search. I tried making a change to the data in the data range and refreshed the pivot table and it still didn't update with the changes, so I am not sure what is happening here.

Thank you,
B

 

[DanteAmor]

The pivot table is a great tool to show different views, filters, totals, columns, rows, etc. of your data.
But unfortunately it is not updated automatically, nor is the source range updated.


If you change the source range or data, you must update the table.

 

[BKGLTS]

Thank you DanteAmor I guess I will have to repost this to get some other input.

 

[sandy666]

you can refresh PT automatically after any change in source table via vba (grrrrr!)
try:

Private Sub Worksheet_Change(ByVal Target As Range)
  Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

 

[DanteAmor]

Thank you DanteAmor I guess I will have to repost this to get some other input.

You can convert your original cell range into a table.
Create the pivot table based on the Table.
After modifying the table, change values, increase columns, increase or decrease rows.
In the pivot table just press the update button.