ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   How to find next value if rows between are empty? (https://www.excelbanter.com/excel-discussion-misc-queries/94380-how-find-next-value-if-rows-between-empty.html)

joes

How to find next value if rows between are empty?
 
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


Bernard Liengme

How to find next value if rows between are empty?
 
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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"joes" wrote in message
oups.com...
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




Dominic LeVasseur

How to find next value if rows between are empty?
 
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

"joes" wrote:

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




All times are GMT +1. The time now is 11:47 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com