VBA - publish the status for each line in a new column in each cell from that column

[myay_87]

Hello,

Can someone help me with a VBA code for below pictures?

In column "Final Status" in this moment i have a lots of "IF" formula to show me the status of the line and i have a lot of columns.

There is a VBA code which can complete each cell with the correct status?

In this moment my formula in the cell is:

=IF(AND(C31<>"",D31="",I31="",J31=""),"Cat 0 P",
IF(AND(C31<>"",D31<>"",I31="",J31=""),"Cat 0",
IF(AND(C31<>"",D31<>"",I31<>"",J31=""),"Cat 3 P",
IF(AND(C31<>"",D31<>"",I31<>"",J31<>""),"Cat 3",""))))

 

[Alex Blakenburg]

Are you sure you need VBA for that ?
If you put this in K2 and copy it down it should give you those results:

=LOOKUP(2, 1/($C2:$J2="x"), $C$1:$J$1)

 

[myay_87]

Thank you Alex for your solution. Now I confront with another situation where Lookup is not helping me.

If I have Cat 1 P and Cat 2 P, the final status should be Cat 1 P and Cat 2 P Planned.
In this moment Lookup is showing me only the final column with x.

With IF formula I can do this but, as I said, the formula it is too big and with many conditions.

 

[Alex Blakenburg]

I don't understand what you are trying to achieve. In your post #1 your last line had 4 x's in the row, what do you want to see as the Final status for that line ? The initial example indicated just the last x column.
Can you confirm you still only have Excel 2016, MS365 gives you more options ?

 

[myay_87]

I'm using Excel 2016 and i cannot upgraded to MS365, now.

Each line from excel it is a project and each project it's divided in four categories (in total I have eleven categories but in the pictures i putted only four).
For each category I have, "Planned (when the category it's planned to start) (ex: Cat 1 P)" and "Finished (when the category it's finished)(ex: Cat 1)".
In "Final Status" I need to know what category is planned or finished but in some cases I have two categories planned and in "Final Status" I need to know what categories are planned.

in this moment in column "Final Status" I have below code which is to beg

 

[Alex Blakenburg]

In post #1 what should be in K for the last row.

 

[myay_87]

Should be Cat 0 P in post #1

 

[Alex Blakenburg]

For the last row with 4 Xs ? That is inconsistent with what you are saying in Post #3 & post #5
You will need to give more examples that make the logic obvious.

 

[bebo021999]

I'd offer an UDF via VBA
Alt-F11 to open VBA window, then insert/ module then paste below function code into:

Option Explicit
Function status(ByVal Heade As Range, ByVal data As Range)
Dim j&, st As String
For j = 2 To 8 Step 2
    If data.Cells(1, j).Value = "x" Then
        st = IIf(st = "", "", st & ", ") & Heade.Cells(1, j).Value & " Finalized"
    ElseIf data.Cells(1, j - 1).Value = "x" Then
        st = IIf(st = "", "", st & ", ") & Heade.Cells(1, j - 1).Value & "lanned"
    End If
Next
status = st
End Function

With C1:J1 is header, C2:J2 is data

try in K2:

=status($C$1:$J$1,C2:J2)

drag down

Book1
	C	D	E	F	G	H	I	J	K
1	Cat 0 P	Cat 0	Cat 1 P	Cat 1	Cat 2 P	Cat 2	Cat 3 P	Cat 3	Final Status
2	x	x		x	x		x	x	Cat 0 Finalized, Cat 1 Finalized, Cat 2 Planned, Cat 3 Finalized
3			x			x			Cat 1 Planned, Cat 2 Finalized
4					x				Cat 2 Planned
5
6
7
8
9
Sheet1

Cell Formulas
Range	Formula
K2:K5	K2	=status($C$1:$J$1,C2:J2)

 

[myay_87]

it's ok.
Thanks