How to find next value if rows between are empty?

  • Thread starter Thread starter joes
  • Start date Start date
J

joes

Hello

I have an excel with an "ID" column (A) and the data column (B). There
exist multiple entries (rows) for the same Id. Unfortunately they did
not multiply out the IDs with the value column means the same entries
are listed together but with no Id. Here an example:

A B
------------
11 DATA_A
12 DATA_B
DATA_C (no id belongs to ID 12)
13 DATA_D
14 DATA_E
DATA_F (no id belongs to ID 14)
DATA_G (no id belongs to ID 14)
15 DATA_H


How can I multiply it out? I like to have this result

A B
------------
11 DATA_A
12 DATA_B
12 DATA_C (no id belongs to ID 12)
13 DATA_D
14 DATA_E
14 DATA_F (no id belongs to ID 14)
14 DATA_G (no id belongs to ID 14)
15 DATA_H

Many thanks
regards

Mark Egloff
 
In C1 (or any cell in row 1) enter =A1
In C2 enter =IF(A2="",C1,A2)
Copy this down as far as the last entry in column B, let's say column C goes
to C100
Now you have the ID's but you want them in column A
Copy C1:C100
Select cell A1 and use Edit |Paste Special -> Values
Now you can delete the formulas in column C
Check that all is correct before saving the file.
best wishes
 
Another play might be to highlight the cells in column A from 1 to the end of
your data.

The select Edit:Goto:Special:Blank Cells.

Type "=" then press the up arrow.

Hit ctrl-enter

HTH
 
Back
Top