Display text based on content of 3 cells

**smugglersblues** · 12-14-2012, 08:13 PM

Hello,
I need to display text based on the content of 3 cells. Here's the example I am using:

A1	D1	F1	H1
ABC123	Use Part XYZ	1F	1FL

H1 represnts the value based on A1, D1 and F1.

Any help is appreciated. ( I did search the forum but didn't find what I'm looking for.)

**Sean Thomas** · 12-14-2012, 08:26 PM

You will need to provide a bit more data than that for anyone to work out any formula,
How do you get to 1FL?
does the other references change?
please provide a workbook with data and the desired outcome

**nemo74** · 12-14-2012, 08:27 PM

Hi there,
What you wrote does not compute in my mind. Your example does not even make sense. How about using a better example and posting what you want the final outcome to be. Right now it sounds like you want you can just =CONCATENATE(A1,D1,F1,H1)

Thanks.

**smugglersblues** · 12-14-2012, 08:49 PM

The content of Part#, Description and Equipment doesn't change. So I want a formula that will match the content of A, B and C in the work book and automatically fill in the text of D based on the first 3 cells.

**Steve N.** · 12-14-2012, 09:30 PM

Here are two possible solutions - they do the same thing but you may find one more user friendly than the other depending on your oveall needs.

The formula in D3 is a VLOOKUP referenced to the PartNum Table in the VLOOKUP tab. Another possible solution is the Named Constant "PN" (stands for 'part number'). It's a LOOKUP formula using the full part number (rather than only the first 3 characters) in column A to return the appropriate result. You'll find the PN formula in the Name Manager.

Hope this is something you can use...

**smugglersblues** · 12-14-2012, 11:09 PM

Hi Steve,
The VLOOKUP looks the most promising here. Is it possible to have the VLOOKUP use 3 cells instead of the one? This issue that I have is that all 3 cells work together to create a unique result (part#, description, and equipment). If any of them are missing from the VLOOKUP then the result will be wrong. Thanks for the help.

**Sean Thomas** · 12-15-2012, 10:08 AM

try this

Sub doUntil1()
'Checks cell contents, until an empty cell is encountered.
'If all 3 cells met a condition then the result will be entered.
Dim rowNo As Integer
rowNo = 1
Do Until IsEmpty(Cells(rowNo, 1))
If Sheet1.Cells(rowNo, 1) Like "ABC123" And Sheet1.Cells(rowNo, 2) Like "5 gal bucket" And Sheet1.Cells(rowNo, 3) Like "Car" Then
Sheet1.Cells(rowNo, 4).Value = "1F"
ElseIf Sheet1.Cells(rowNo, 1) Like "ADC321" And Sheet1.Cells(rowNo, 2) Like "25 gal bucket" And Sheet1.Cells(rowNo, 3) Like "Tractor" Then
Sheet1.Cells(rowNo, 4).Value = "2F"
ElseIf Sheet1.Cells(rowNo, 1) Like "EDK293" And Sheet1.Cells(rowNo, 2) Like "10 gal bucket" And Sheet1.Cells(rowNo, 3) Like "Truck" Then
Sheet1.Cells(rowNo, 4).Value = "3F"
ElseIf Sheet1.Cells(rowNo, 1) Like "REA322" And Sheet1.Cells(rowNo, 2) Like "15 gal bucket" And Sheet1.Cells(rowNo, 3) Like "Scooter" Then
Sheet1.Cells(rowNo, 4).Value = "4F"
End If
rowNo = rowNo + 1
Loop
End Sub

**smugglersblues** · 12-15-2012, 02:01 PM

Hi Sean,
Is it possible to add error handling in the macro so if I have a blank row or a row that doesn't match the content it will continue with the loop?

**Sean Thomas** · 12-16-2012, 04:51 AM

This should work if items do not match, it just wont give a result. but it will stop if it encounters a blank cell.

I could change it so it searches a range rather than just completed rows.
How many rows are likely to be used?

**Sean Thomas** · 12-16-2012, 05:04 AM

This one does the trick.
Set the range for as many cells as you are likely to need.

Sub ForEachCellInRange()
Dim iCell As Range
Dim rowNo As Integer
For Each iCell In Sheet1.Range("A1:A20")
    rowNo = iCell.Row
    If Sheet1.Cells(rowNo, 1) Like "ABC123" And Sheet1.Cells(rowNo, 2) Like "5 gal bucket" And Sheet1.Cells(rowNo, 3) Like "Car" Then
        Sheet1.Cells(rowNo, 4).Value = "1F"
    ElseIf Sheet1.Cells(rowNo, 1) Like "ADC321" And Sheet1.Cells(rowNo, 2) Like "25 gal bucket" And Sheet1.Cells(rowNo, 3) Like "Tractor" Then
        Sheet1.Cells(rowNo, 4).Value = "2F"
    ElseIf Sheet1.Cells(rowNo, 1) Like "EDK293" And Sheet1.Cells(rowNo, 2) Like "10 gal bucket" And Sheet1.Cells(rowNo, 3) Like "Truck" Then
        Sheet1.Cells(rowNo, 4).Value = "3F"
    ElseIf Sheet1.Cells(rowNo, 1) Like "REA322" And Sheet1.Cells(rowNo, 2) Like "15 gal bucket" And Sheet1.Cells(rowNo, 3) Like "Scooter" Then
        Sheet1.Cells(rowNo, 4).Value = "4F"
    End If
Next iCell
End Sub

**Sean Thomas** · 12-16-2012, 05:05 AM

This one does the trick.
Set the range for as many cells as you are likely to need.

Sub ForEachCellInRange()
Dim iCell As Range
Dim rowNo As Integer
For Each iCell In Sheet1.Range("A1:A20")
    rowNo = iCell.Row
    If Sheet1.Cells(rowNo, 1) Like "ABC123" And Sheet1.Cells(rowNo, 2) Like "5 gal bucket" And Sheet1.Cells(rowNo, 3) Like "Car" Then
        Sheet1.Cells(rowNo, 4).Value = "1F"
    ElseIf Sheet1.Cells(rowNo, 1) Like "ADC321" And Sheet1.Cells(rowNo, 2) Like "25 gal bucket" And Sheet1.Cells(rowNo, 3) Like "Tractor" Then
        Sheet1.Cells(rowNo, 4).Value = "2F"
    ElseIf Sheet1.Cells(rowNo, 1) Like "EDK293" And Sheet1.Cells(rowNo, 2) Like "10 gal bucket" And Sheet1.Cells(rowNo, 3) Like "Truck" Then
        Sheet1.Cells(rowNo, 4).Value = "3F"
    ElseIf Sheet1.Cells(rowNo, 1) Like "REA322" And Sheet1.Cells(rowNo, 2) Like "15 gal bucket" And Sheet1.Cells(rowNo, 3) Like "Scooter" Then
        Sheet1.Cells(rowNo, 4).Value = "4F"
    End If
Next iCell
End Sub

**Ghozi Alkatiri** · 12-16-2012, 05:56 AM

try this one
=IF(COUNTIF(A$2:A2;A2)=1;SUMPRODUCT((1/COUNTIF($A$2:$A2;A$2:A2)))&"F";INDEX(E$1:E1;MATCH($A2;$A$1:$A1;0)))

copy down

**smugglersblues** · 12-16-2012, 07:43 AM

Sean,
Thanks for you help. This is doing exactly what I need it to do. One last question. How would I set the code to populate Cell 4 and Cell 5 on occasion?

If Sheet1.Cells(rowNo, 1) Like "ABC123" And Sheet1.Cells(rowNo, 2) Like "5 gal bucket" And Sheet1.Cells(rowNo, 3) Like "Car" Then
Sheet1.Cells(rowNo, 4).Value = "1F"

How can I tell this code to also fill rowNo, 5 with the same information? Can it be added with this same line of code, or would I need a separate line of code with rowNo, 5?

**Sean Thomas** · 12-16-2012, 09:02 AM

Just add another line

If Sheet1.Cells(rowNo, 1) Like "ABC123" And Sheet1.Cells(rowNo, 2) Like "5 gal bucket" And Sheet1.Cells(rowNo, 3) Like "Car" Then
Sheet1.Cells(rowNo, 4).Value = "1F"
Sheet1.Cells(rowNo, 5).Value = "1F"

**smugglersblues** · 12-20-2012, 12:25 PM

Hi Sean,
Everything you have posted works great. I have one small issue left here so I hope you can help me out again. I have my macro saved in personal.xls. When I open a workbook and try to use the macro, it does nothing. If I copy the the macro to the workbook, it works. Shouldn't the macro work from the personal workbook on another workbook without copying it over?

Display text based on content of 3 cells

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Display text based on content of 3 cells

Re: Display text based on content of 3 cells

Re: Display text based on content of 3 cells

Re: Display text based on content of 3 cells

Re: Display text based on content of 3 cells

Re: Display text based on content of 3 cells

Re: Display text based on content of 3 cells

Re: Display text based on content of 3 cells

Re: Display text based on content of 3 cells

Re: Display text based on content of 3 cells

Re: Display text based on content of 3 cells

Re: Display text based on content of 3 cells

Re: Display text based on content of 3 cells

Re: Display text based on content of 3 cells

Re: Display text based on content of 3 cells

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions