Dear All |
I want to check if the first 8 consecutive cells in column B match the first 8 consecutive cells in any of the columns D through W. If a match is found, we return the column number; otherwise, return "No Match". |
Any assistance would be greatly appreciated |
Regards |
George |
Matching Column
- Thread starter geo Jul
- Start date
p45cal
Well-Known Member
try:
If there are multiple matces it will return all of them.
It could be tweaked a bit to deal with more than just 8 rows by changing the =8 in the formula to =ROWS(First8)
You could make this formula into a named lambda formula in Name Manager.
Create a new Name and make its definition:
then on the spreadsheet, use it like this:
See cell AA7 and AA8 in the attached.
Code:
=LET(First8,B2:B9,range,D2:W9,TEXTJOIN(", ",TRUE,FILTER(TEXTBEFORE(ADDRESS(1,COLUMN(range),4),"1"),BYCOL(range,LAMBDA(a,SUM(--(First8=a))))=8,"No match")))It could be tweaked a bit to deal with more than just 8 rows by changing the =8 in the formula to =ROWS(First8)
You could make this formula into a named lambda formula in Name Manager.
Create a new Name and make its definition:
Code:
=LAMBDA(First8,range,TEXTJOIN(", ",TRUE,FILTER(TEXTBEFORE(ADDRESS(1,COLUMN(range),4),"1"),BYCOL(range,LAMBDA(a,SUM(--(First8=a))))=ROWS(First8),"No match")))then on the spreadsheet, use it like this:
See cell AA7 and AA8 in the attached.
Last edited:
if I want more columns, how can I amend your genius formula
I am thankful for the support you have given and Your assistance has had a significant impact many thanks
Last edited:
p45cal
Well-Known Member
A bit more depth on this please.
Again, thank you for your help
p45cal
Well-Known Member
?!
What I meant by that is for you to explain more fully what you want to happen when you say:
What I meant by that is for you to explain more fully what you want to happen when you say:
thank you for your time and reply have a wonderful day