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,857
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,857
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,857
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,800
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,857
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,082,151
Messages
5,363,434
Members
400,737
Latest member
vipamuk

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top