Find all matches and consolidate them

BKGLTS

Board Regular
Joined
Aug 27, 2018
Messages
78
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
ABC
1M1
2
Twigs
2M11Berries
3
4M23Twigs
5

<tbody>
</tbody>


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.

AB
1Twigs5
2Berries1
3
4
5

<tbody>
</tbody>


Thank you,
B
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,784
Office Version
2007
Platform
Windows
For that you can use a pivot table:

 

BKGLTS

Board Regular
Joined
Aug 27, 2018
Messages
78
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,784
Office Version
2007
Platform
Windows
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

Board Regular
Joined
Aug 27, 2018
Messages
78
Not exactly working as I need, will post shortly what issues I am running into. Thank you!
 

BKGLTS

Board Regular
Joined
Aug 27, 2018
Messages
78
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,784
Office Version
2007
Platform
Windows
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

Board Regular
Joined
Aug 27, 2018
Messages
78
Thank you DanteAmor I guess I will have to repost this to get some other input.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,231
you can refresh PT automatically after any change in source table via vba (grrrrr!)
try:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache.Refresh
End Sub
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,784
Office Version
2007
Platform
Windows
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.
 

Forum statistics

Threads
1,077,919
Messages
5,337,183
Members
399,131
Latest member
Vinnyjuice

Some videos you may like

This Week's Hot Topics

Top