Forum Discussion
Compare 2 columns and extract data to another sheet
=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?
=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.
- OliverScheurich 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 ?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.
=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 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.
