# Excel Conditional Column Input

From the sample spreadsheet attached, I'd like some help with a macro or statement which as soon as a tag number is entered on the right side of the fill bar in the TAG NO field, it looks for and matches the same tag number on the left side of the sheet, and then populates the rest of the columns to match the tag number.  If there's no match, I'd like the tag number column on the right to return "no match."
Palletized-Product-Status.xls
###### Who is Participating?

Commented:
Try this formula in Q2 copied down the column

=IF(P2="","",IF(COUNTIF(\$A\$2:\$A\$5000,\$P2),IF(INDEX(B\$2:B\$5000,MATCH(\$P2&"",\$A\$2:\$A\$5000,0))="","",INDEX(B\$2:B\$5000,MATCH(\$P2&"",\$A\$2:\$A\$5000,0))),"No Match"))

and in R2 copied across to AC2 and down all those columns

=IF(\$P2="","",IF(\$Q2="No Match","",IF(COUNTIF(\$A\$2:\$A\$5000,\$P2),IF(INDEX(C\$2:C\$5000,MATCH(\$P2&"",\$A\$2:\$A\$5000,0))="","",INDEX(C\$2:C\$5000,MATCH(\$P2&"",\$A\$2:\$A\$5000,0))),"No Match")))

see attached

regards, barry
26888994.xls
0

Commented:
You just need a simple conditional vlookup statement, like

[Q2] =IF(ISNA(VLOOKUP(\$P2,\$A:\$N,COLUMN(B\$2),0)),"No Match",VLOOKUP(\$P2,\$A:\$N,COLUMN(B\$2),0))

and this can be copied across the columns to the right, and/or down.  As you have formatted, it depends on the order of the columns on the left.  As your columns on the left are the same, this VLOOKUP is a simple approach.

You only need a macro if you want the Tag No column to show "No Match" and that would be tough on the user, overwritting the number input with "No Match".  As an alternative, this formula fills all the columns to the right of Tag No with "No Match".

PS - you might also consider adding a Data Validation Field on the Tag No entry field, that way, only valid data can be pulled up on the right - never a "No Match" case!

See attached.

Enjoy!

Dave
Palletized-Product-Status-r1.xls
0

Sr. Project ManagerCommented:
If you have empty cells within the same row and you don't want to add the ";" to the cells then you can also try this -
this code will skip empty cells.
``````Sub addme()
For i = 1 To Cells(65536, "A").End(xlUp).Row
If Right(Range("A" & i), 1) <> ";" And Range("A" & i) <> "" Then
Range("A" & i) = Range("A" & i) & ";"
End If
Next
End Sub
``````
0

Sr. Project ManagerCommented:
sorry wrong post... pls ignore.
0

Commented:
@ mrherndon - I'm curious if you tried my solution.

Cheers,

Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.