I need help on creating an Excel Formula 1

[frog40]

I have an Excel spreadsheet that I need to have automatically edited. I do not know the formula to do this. Here is the scenerio:
Col 1 Col 2
Site Nbr Name

123456 Jane B
John A
George A
Georgia B

All of the above Names need to have the same site number recorded in Col 1 to identify the name with the Site Nbr. The above example is only one group. There are several other groups like this one that has different Site Nbrs for each group. There are also over 2000 rows. This spreadsheet has been exported from another site, so I can only change it from my end.

I know I can copy the Site Nbr and paste it in the rows below, but with over 2000 rows, and different groups in between, this takes a lot of time.

I would like to have a formula for Excel to fill in Col 1 for me.

I am running Vista & Win7, and Office 2007 & 2010 (on different pc's). Thank you for any help you can give me.

 

[Scott24x7]

Frog40,
How do you determine what site a person belongs to? If you have a common link between the two sheets (your 2000 row one, and the one that has the site data) you can use VLOOKUP or HLOOKUP (if they are horizontal) to match the criteria, and then place the result in the field next to the person. So if you have something like site name or a way you can relate the two data, then you can use this. If you can only relate it by "knowing" who belongs where, then your only option will be to do it manually. I suggest seeing if you can find a common linking field between the two.
Look at VLOOKUP in help to see how this works. But it's syntax is basically:
=VLOOKUP(Lookup_Value (What you want to lookup), Array (where to look), column from the array to return if it does find it, Exact match/Yes or no)

So an example might look like this:
=VLOOKUP(C3,Sheet2!A120,2,TRUE)

Note that in the area you are looking for your matching data (array) this can be a real array, or a range on the same sheet, or a range on another sheet. In this example, I use a range from another sheet. the A120 says I'm looking in those cells, but it will only search the first column of the array for whatever value is in C3 that I'm searching on. So you always have to have your first column as the data that is being searched. the ,2 after D20 says, give me what ever value is in column2 (B). If I wanted the value from column D, it would be ,4 instead.
Hope this helps.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[frog40]

Unfortunately, there is no link. I only know that the names in Col 2 are associated with the Site Nbr in Col 1. When the Site Nbr in Co.1 changes, then those names in Col 2 are associated with that Site Nbr until a different Site Nbr shows up in Col.1, etc.

I might try creating 2 worksheets and then use the VLOOKUP on one of them. I sure don't want to do this manually.

Thanks again

 

[Kurupt55]

If I am following this correctly then your data looks like this:

123456   Jane B
         John A
         George A
         Georgia B
654321   Peter X
         Percy Y
         Sally S

If this is the case and all the people below 123456 are in that site and all the people under 654321 are in that site then a Simple VBA code will do the trick nicely
something like this

Sub copydown()
    For n = 1 To Application.WorksheetFunction.CountA(Range("B:B"))
        If Range("A" & n).Value = "" Then Range("A" & n).Value = Range("A" & n - 1).Value
    Next n
End Sub

Impossible is Nothing

 

[Kurupt55]

Actually After thinking about it, If you dont like using VBA then you could use this formula in Column C
=if(A2="",A1,C1)
then just copy down to the end of your data and then copy column C into Column A using paste special -> Values

Impossible is Nothing

 

[Kurupt55]

Sorry formula is wrong, Should be:
=IF(A2="",C1,A2)
You will have to manually enter the first site code into cell C1

Impossible is Nothing

 

[Scott24x7]

Frog40,
If this reliant on information that only you know, there won't be much in the way of options. I suggest you try to build a way in a "staging" sheet to relate the two. Otherwise, you have to rely on what you know about the data, and you can't automate that.
Good luck.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Gavona]

An alternative without VBA (though Kurupt55's is good):
Select from the first cell containing a blank to the last one.
Edit, GoTo, Special, Blanks
=[uparrow] Ctrl-Enter

Then you will probably want to select the entire column and copy the formulae to values.

With VBA:

Sub Macro1()
    Dim r As Range
    Set r = Selection
    r.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    r.Value = r.Value
End Sub

OR avoiding the need to select and assuming that A2 is the first cell and you want to fill blanks down to the last used row in the workbook

Sub Macro2()
    Dim r As Range
    Set r = [A2]
    Set r = Range(r, Cells(ActiveSheet.UsedRange.Rows.Count, r.Column))
    r.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    r.Value = r.Value
End Sub

All these work equally well if you are doing the same for several columns.

Gavin

 

[frog40]

Gavona, I tried =[uparrow] Ctrl-Enter, but kept getting "There is an error in your formula" message. I tried several diff scenarios, but always had the same results. I then tried to locate "uparrow" in the help section, but it kept coming up there that this could not be found.

I am using Win7 and Office 2010 to test this. Could this be the problem?

 

[Gavona]

I meant the key that moves the cursor up. If Numlock is off then [uparrow] is the 8 key on my keyboard.

Gavin

 

[frog40]

Thanks Gavona, now it works. Sorry I did not understand what =[uparrow] meant.