Compare 2 columns and extract data to another sheet

New Contributor

I am looking for a formula or a short code for 

comparing column 5 (Unique) values with column 4 in Sheet 1 of Workbook  and if they are equal that row should be copied to Sheet 2 which will be my output sheet. 

 

Column 5 Will have a unique value 

Column 4 might have duplicates  

 

the below is an example data set - the values will be of 6 or 8 digit random numbers for column 5 and 4

 

karth1109_0-1639660540681.png

 

7 Replies

@karth1109 

=FILTER(A1:D17;(D1:D17=45)+(D1:D17=46)+(D1:D17=47)+(D1:D17=48)+(D1:D17=10))

 

With Office365 or 2021 you can apply Filter function. With other versions of excel you can use advanced filter.

Thanks for the help , but actually my column 5 will have 100-200 unique values which needs to check against 49000 values in Column 4- is it possible to have range of the column in place of number 45 , 46 , 47, 48?

@karth1109 I would opt for Power Query. Connect to the file with 49000 entries. Likewise, connect the list of 100-200 values you want to extract. Then merge the the two.

 

Are you familiar with PQ? If not, the link below would be a good place to start.

https://exceloffthegrid.com/power-query-introduction/ 

@karth1109 

=OR(AND(D2>=1,D2<=100),D2=200,D2=300,D2=400)

 

With above formula you can enter ranges of numbers or any individual numbers as in (filter criteria) range G1:G2 in attached sheet. I did advanced filter for 5000 rows in columns A to D and it works in my spreadsheet. To verify the filter result i applied SUMPRODUCT formula for columns A to C. The sumproduct formulas are in cells O1:Q1.

@karth1109 

=FILTER(A2:D17;((D2:D17>=45)*(D2:D17<=48))+(D2:D17=10))

 

With Office365 or 2021 it is above formula i should say in addition.

@Quadruple_Pawn thanks for the these formulas , the above formulas are using continues numbers in the set given . In case if each cell has a random 5 or 8 digit number which will be my unique value (about 100 of them ) the above formulas will not work so ideally for my case each cell of column 5 should be checked for matching value/values in column 4 - extract that row and publish the data in other sheet
so should it be done in VB or some kind of formula still works for it ?

@karth1109 

If one can solve this with VBA or Power Query that would probably be the best way.

I can't do this with either way but i can suggest a makeshift solution.

 

In attached file in sheet "Tabelle2" i entered values in Column Z (this can easily be done with autofill) and used a formula to concatenate these values in Column AA. I copied the entry of cell AA200 and pasted only values in cell G4 and wraped it in an OR formula in cell G4. Now range G3:G4 is the criteria range for advanced filter. I verified the filter result with a nested sum formula in cells O4 and P4.