Find all matches and consolidate them

BKGLTS

Board Regular
Joined
Aug 27, 2018
Messages
81
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
8,733
Office Version
2007
Platform
Windows
For that you can use a pivot table:

 

BKGLTS

Board Regular
Joined
Aug 27, 2018
Messages
81
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
8,733
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
81
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
81
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
8,733
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
81
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,707
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
8,733
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,081,417
Messages
5,358,553
Members
400,503
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top