Conditionally auto fill blank cells in excel

Question

I have data that looks like this...

1 A
1
1
1
2 B
2
2
2
3
3
3
4 C
4
4

It continues for 38,000 rows, and I need to auto fill the blank cells in column B with the initial value in column B associated with the unique values in column A, if one exists, and insert "N/A" if there is no value associated with the unique number in Column A. The result would look like this...

1 A
1 A
1 A
1 A
2 B
2 B
2 B
2 B
3 N/A
3 N/A
3 N/A
4 C
4 C
4 C

Is there any way to do this in excel? If not, any guidance with other programs would be helpful as well. Original data is in .CSV, but is not tidy. Excel sheet is tidy, but incomplete as displayed above.

Answer 1

1. Copy that two coloumns and paste it in another sheet
2. Filter for blanks 2nd coloumn
3. Delete filtered blank cells Now you're left with letters on the right and associated numbers on the left. It's a reference table for you vlookup.
4. Put vlookup function (either on the A letter or (to be carefull) in 3rd coloumn next to the A letter) and drag it down - that's all, simple and easy

Answer 2

You could use Index,Match or VLookup (I prefer Index,Match)

Basically the way I'm looking at your problem you could have another sheet in the workbook with the data you need to autofill and the page with the actual output...Example (We'll call this one 'Sheet2', I put in the headers for less confusion):

     A    B    C    D
1    1    A 
2    2    B 
3    4    C
4

Then on the Page where you need the data autofilled (Sheet1):

     A    B
1    1    `=INDEX(Sheet2!$B:$B,MATCH(Sheet1!A1,Sheet2!$A:$A,0))` (Just handle-drag this down)
2    1 
3    1 
4    1 
5    2  
6    2 
7    2 
8    2 
9    3 
10   3 
11   3 
12   4 
13   4 
14   4 

It isn't as Complicated as it looks and it will give you #N/A Errors when the data isn't available!:

Answer 3

You can write in B1 just 1A like you wrote or =A1 it will have the same value, in B2 you have to write this Formula:
=IF(LEFT(A2,1)=LEFT(A1,1),B1,IF(LEN(A2)=1,A2&" N/A",A2))
If your Data is like what is written above no empty cells, you will have the same result and you can drag the formula down it will change the references automatically. This formula check if the first character between 2 consecutive rows is the same it will write the same value in column B, if not the second if check if only one characters is in the cell it will add N/A else it is more then one it will write it in B