Help: Excel

[Gooberlx2]

I have a worksheet where the Data goes something like:

example.PNG

Is there a way I can reference the Site# from column A using an ID# from column C? For example:

If I wanted ID# 33333 the return would be SAAAAA. Where 66666 would return SBBBBB.

Unfortunately, since the Site#'s aren't copied in to every row for their corresponding ID#s, I can't figure out how to use the lookup functions. This is a huge spreadsheet that several people use, so modifying it like that isn't an option.

Any help would be very very appreciated.

 

[Kmart]

Might could be done by using IF functions. =IF(B3="Your.Data.Value",A2,IF(B4="Your.Data.Value",A2)) etc...

There are also ways to use the lookup command if you have many conditions or criteria, ex:

=LOOKUP(A2,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"})

That lookup formula could be used for assigning cell A2 a grade based on the criteria.

 

[tjcinnamon]

You could populate all of the Site# and that would make it easier.

Run this macro in the sheet where all the data is.

Do it on a copy!!!


Sub createSiteID()

For x = 1 To ActiveSheet.UsedRange.Rows.Count
Dim a As String
If Cells(x, 1) <> "" Then
a = Cells(x, 1)
Else
Cells(x, 1) = a
End If

Next x

End Sub

OUTPUT:

SAAAA
SAAAA 1111-AAA 11111
SAAAA 2222-BBB 22222
SAAAA 3333-CCC 33333
SBBBB
SBBBB 4444-DDD 44444
SBBBB 5555-EEE 55555
SBBBB 6666-FFF 66666

 

[Gooberlx2]

Originally posted by: tjcinnamon
You could populate all of the Site# and that would make it easier.

Run this macro in the sheet where all the data is.

Do it on a copy!!!


Sub createSiteID()

For x = 1 To ActiveSheet.UsedRange.Rows.Count
Dim a As String
If Cells(x, 1) <> "" Then
a = Cells(x, 1)
Else
Cells(x, 1) = a
End If

Next x

End Sub

OUTPUT:

SAAAA
SAAAA 1111-AAA 11111
SAAAA 2222-BBB 22222
SAAAA 3333-CCC 33333
SBBBB
SBBBB 4444-DDD 44444
SBBBB 5555-EEE 55555
SBBBB 6666-FFF 66666

Yeah, that was the easiest solution. I ran it on copied/pasted data in a new speadsheet. Thanks for the help. I really need to start learning VB so I can write my own macros.

 

[tjcinnamon]

With this solution you can make it work on a cell change for a peticular column. I don't have 2003 in front of me, but I can test it on 2007 later.

If you type the ID you are looking for in cell E1, you could use the Match function to find the row in column C where the value is. Then you can use that row number and column number 1 and use the .End(xlUp)

Sub findSiteId()
Dim lRow As Long
Dim vMatch As Variant

On Error Resume Next
With Application.WorksheetFunction
vMatch = .Match(Range("E1").Value, Range("C:C"), 0)
End With
On Error GoTo 0

If Not IsEmpty(vMatch) Then
lRow = CLng(vMatch)
Range("F1").Value = Cells(lRow, 1).End(xlUp).Value
Else
Range("F1").Value = "NOT FOUND"
End If
End Sub