Last cell/column in a row with a paticular data

akshadd

New Member
Joined
Jul 5, 2010
Messages
2
Hi All,

I have dynamnic no of columns for a row .
Except a first few columns for that row ,all successive columns will have data as either PASS or FAIL(see eg below). is there any excel function which can tell me which is the last column/cell with either PASS or FAIL.

e.g :
COL1 COL2 COL3 COL4 COL5 COL6
ABC 123 PASS FAIL PASS FAIL

Thanks
Akshay
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,158
Office Version
2019
Platform
Windows
Welcome to the board!

Is this what you're looking for?
Excel Workbook
ABCDEFGH
1ABC123PASSFAILPASSFAIL
2ABC124PASSFAILFAILPASS
3ABC125PASSFAILPASSFAIL
4ABC126PASSFAILFAILPASS
Sheet
 

akshadd

New Member
Joined
Jul 5, 2010
Messages
2
Hi Jason,

Thanks a ton for ur quick reply.
Well actually i am looking for the last column with data (PASS OR FAIL) , i.e in your example COL6 is the expected answer. I wanted to know at which column number the dynamic columns have ended .

Actually i will be having dynamic columns for a row with PASS or FAIL as its only data, i require the last column , that column no. will be same for all other rows .
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,158
Office Version
2019
Platform
Windows
If I understand what you're asking for correctly, try

="Col "&MAX(IF(((C1:F4="PASS")+(C1:F4="Fail"))>0,COLUMN(C1:F4),0))

I think that's what you need.
 

Forum statistics

Threads
1,082,569
Messages
5,366,360
Members
400,886
Latest member
Fchel

Some videos you may like

This Week's Hot Topics

Top