Consolidate data from two sheets to a single sheet based on column criteria

mountainman88

Board Regular
Joined
Jun 22, 2019
Messages
109
Office Version
  1. 2016
Platform
  1. Windows
I have 3 sheets: Sheer A, Sheet B and a sheet called Consolidation.

Using a single formula in A1 of Consolidation that I can drag across and down how can I pull just the rows of A and B that contain the text ‘import’ in Column B. Let’s say we only need columns A to H to be brought in (not the whole row).

If sheet A had 100 rows and 50 with the text ‘import’ in Column B;

and sheet B had 60 rows and 25 wijt the text ‘import’ in Column B

Then Consolidation end result would pull 75 total lines (columns A to H): the 50 import lines from Sheet A followed by the 25 import lines from Sheet B.

thanks
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi Mountain,

Adjust the ranges as needed, this formula allows for 100 rows only in Sheet A & B.

Make sure to drag down enough rows to return total rows needed.

Book1
ABCDEFGH
11/08/2021ExportTestA1TestA1TestA1TestA1TestA1TestA1
22/08/2021ImportTestA2TestA2TestA2TestA2TestA2TestA2
33/08/2021ExportTestA1TestA1TestA1TestA1TestA1TestA1
44/08/2021ImportTestA2TestA2TestA2TestA2TestA2TestA2
55/08/2021ImportTestA2TestA2TestA2TestA2TestA2TestA2
66/08/2021ExportTestA1TestA1TestA1TestA1TestA1TestA1
77/08/2021ImportTestA1TestA1TestA1TestA1TestA1TestA1
Sheet A


Book1
ABCDEFGH
11/02/2021ExportTestB1TestB1TestB1TestB1TestB1TestB1
22/02/2021ImportTestB2TestB2TestB2TestB2TestB2TestB2
33/02/2021ExportTestB1TestB1TestB1TestB1TestB1TestB1
44/02/2021ImportTestB2TestB2TestB2TestB2TestB2TestB2
55/02/2021ImportTestB2TestB2TestB2TestB2TestB2TestB2
66/02/2021ExportTestB1TestB1TestB1TestB1TestB1TestB1
77/02/2021ExportTestB1TestB1TestB1TestB1TestB1TestB1
Sheet B


Book1
ABCDEFGH
12/08/2021ImportTestA2TestA2TestA2TestA2TestA2TestA2
24/08/2021ImportTestA2TestA2TestA2TestA2TestA2TestA2
35/08/2021ImportTestA2TestA2TestA2TestA2TestA2TestA2
47/08/2021ImportTestA1TestA1TestA1TestA1TestA1TestA1
52/02/2021ImportTestB2TestB2TestB2TestB2TestB2TestB2
64/02/2021ImportTestB2TestB2TestB2TestB2TestB2TestB2
75/02/2021ImportTestB2TestB2TestB2TestB2TestB2TestB2
Consolidation
Cell Formulas
RangeFormula
A1A1=IF(ROWS(A$1:A1)>COUNTIF('Sheet A'!$B$1:$B$100,"Import"),IF(ROWS(A$1:A1)-COUNTIF('Sheet A'!$B$1:$B$100,"Import")>COUNTIF('Sheet B'!$B$1:$B$100,"Import"),"",INDEX('Sheet B'!A$1:A$100,SMALL(IF('Sheet B'!$B$1:$B$100="Import",ROW('Sheet B'!$B$1:$B$100)),ROWS(A$1:A1)-COUNTIF('Sheet A'!$B$1:$B$100,"Import")))),INDEX('Sheet A'!A$1:A$100,SMALL(IF('Sheet A'!$B$1:$B$100="Import",ROW('Sheet A'!$B$1:$B$100)),ROWS(A$1:A1))))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top