Complex-ish IF statement

[jamorin]

Howdy folks. Here is the problem.

Three columns of data labeled Tract, Block and Lot.

What the Data to display as follows:
T#B#L# when each column has data
B#L# when there is only data in columns the Block and Lot columns
L# when there data only in the Lot Column

As you can see below, I have an IF statement that does this fine.

Where I would like some help is the next step where the combined output will only put information for the columns that apply such as:

T#L# when there is just data in the Tract and Lot columns, or
T#B# when there is only data in the Tract and Block Columns

I figure it is one more IF statement but I'm getting lost

As a bonus I'd love to know how to do it in VBA as well.

Excel Workbook
	A	B	C	D	E
1	Tract	Block	Lot	Combined
2	537	12	5	T537B12L5
3		12	5	TB12L5
4			5	L5
5	611	2	4	T611B2L4
6	412	41	16	T412B41L16
7		15	12	TB15L12	This one is not correct
8		27	47	TB27L47	This one is not correct
9	100	51		T100B51L	This one is not correct
10	4	62	3	T4B62L3
Sheet1

 

[scottylad2]

Maybe this? I'm saying maybe as i'm not sure how the cells you marked yellow can be incorrect if the cell I've marked green you left as correct? they look the same to me. Also is there ever a case just a T and an L?

Excel Workbook
	A	B	C	D	E
1	Tract	Block	Lot	Combined	Formulas
2	537	12	5	T537B12L5	T537B12L5
3		12	5	TB12L5	B12L5
4			5	L5	L5
5	611	2	4	T611B2L4	T611B2L4
6	412	41	16	T412B41L16	T412B41L16
7		15	12	TB15L12	B15L12
8		27	47	TB27L47	B27L47
9	100	51		T100B51L	T100B51
10	4	62	3	T4B62L3	T4B62L3
Sheet3

 

[scottylad2]

With only T and L

Excel Workbook
	A	B	C	D	E
1	Tract	Block	Lot	Combined	Formulas
2	537		5	T537B12L5	T537L5
3		12	5	TB12L5	B12L5
4			5	L5	L5
5	611	2	4	T611B2L4	T611B2L4
6	412	41	16	T412B41L16	T412B41L16
7		15	12	TB15L12	B15L12
8		27	47	TB27L47	B27L47
9	100	51		T100B51L	T100B51
10	4	62	3	T4B62L3	T4B62L3
Sheet3

Can't help with VBA but should be easy for some of the gurus on here

 

[jamorin]

Rows 7 and * are "wrong" in that "T" should not be showing up. I only what the Combined Column to to have entries if there is Corresesponding data in the Previous Columns.

Said Differently by Examples:

Tract 212, Block 3, Lot 2 would be: T212B3L2
Tract 212, Lot 2 would be: T212L2
Tract 212, Block 3 would be T212B3
Tract 212 would be: T212
Block 3 would be: B3
Lot 2 would be: L2
Block 3, Lot 2 would be: B3L2

 

[jamorin]

BTW Scotty Thanks!

...your IF statement does the trick. Now I wonder how to code that in VBA : )

 

[ret44]

I believe this will do it with VBA - test in a copy of your workbook.

Dim i As Integer

i = 2

Do Until Cells(i, 1).Value="" And Cells(i, 2).Value ="" And Cells(i, 3).Value = ""
 ' Tests for values in cells to make sure there's data there

If Not Cells(i, 1).Value = "" Then
Cells(i, 4).Value = "T" & Cells(i, 1).Value 'Adds Tract Value if not Blank
End If

If Not Cells(i, 2).Value = "" Then
    Cells(i, 4).Value = Cells(i, 4).Value & "B" & Cells(i, 2).Value 'Add Block Value if not blank
    End If

If Not Cells(i, 3).Value = "" Then
    Cells(i, 4).Value = Cells(i, 4).Value & "L" & Cells(i, 3).Value ' Adds lot Value if not blank
    End If

i = i + 1 ' Next Iteration

Loop

 

[jamorin]

Correction...

Rows 7 and 8 are "wrong" in that "T" should not be showing up. I only want the Combined Column to to have entries if there is Corresesponding data in the Previous Columns. (I had correct in places I shouldn't have! doh)

Said Differently by Examples:

Tract 212, Block 3, Lot 2 would be: T212B3L2
Tract 212, Lot 2 would be: T212L2
Tract 212, Block 3 would be T212B3
Tract 212 would be: T212
Block 3 would be: B3
Lot 2 would be: L2
Block 3, Lot 2 would be: B3L2

 

[jamorin]

ret44 - I am getting a compile error. It is probably my fault. I open the VBA editor (ALT F11) and added a Module and pasted in your code.

Windows Vista Business SP2 32-bit, Office 2007

 

[ret44]

Hi Jamorin,

What is the error?

I did not but sub tags on the code, so you would need to put

Sub Macroname() before the first line of code

and

End Sub after the last line of code

If that wasn't the issue, I may be able to help if I know the error message. It did work on my machine.

 

[jamorin]

Thanks RET - That was my issue just didn't enter it correctly. Since I have your attention, is there a way to have this run automatically everytime an entry is changed (like the IF statement does)?