![]() |
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 |
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 |
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