in sheet bm1:
i have to write "ptc" in H column and "1" in I column if sheet bm2 columns (G,R,S,T) contains row with same values like C,E,F,G columns.
for example row 6 in bm1 matches row 12 in bm2
example.xlsx
in sheet bm1:
i have to write "ptc" in H column and "1" in I column if sheet bm2 columns (G,R,S,T) contains row with same values like C,E,F,G columns.
for example row 6 in bm1 matches row 12 in bm2
example.xlsx
Last edited by pugulis; 01-11-2016 at 04:03 PM.
In Bm1 column A
=C2&E2&F2&G2
In Bm2 column A
=G2&R2&S2&T2
in Bm1 column H
=IFERROR(IF(VLOOKUP(A2,'bm2'!A2:A170,1,0)+0,"pct",""),"")
in Bm1 column I
=IF(H2<>"",1,"")
Copy all formulas down columns
Formula:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Thank you very much!
Don't you need to put A2:A170 in dollar signs here?
=IFERROR(IF(VLOOKUP(A2,'bm2'!$A$2:$A$170,1,0)+0,"pct",""),"")
Yes. Have you tried the other formula offered?
Yeah, tried both variants, both work perfectly well!
File size in 1st variant with 9000 rows - 13.5mb
File size in 1st variant with 9000 rows - 14.5mb
Yours is more easy to use but uses more memory. Thanks guys! You are genius!
You're welcome. Thanks for the rep.
It would probably be smaller if you changed the full column references to defined ranges, for example, $G$2:$G$? instead of $G:$G
Regards, TMS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks