Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel Conditional Column Input

Posted on 2011-03-15
5
Medium Priority
?
331 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:mrherndon
  • 2
  • 2
5 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 35142007
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
 
LVL 42

Expert Comment

by:dlmille
ID: 35142010
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
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 35142012
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

Open in new window

0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 35142016
sorry wrong post... pls ignore.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35176986
@ mrherndon - I'm curious if you tried my solution.

Cheers,

Dave
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question