Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate cells, replacing blanks with cell contents from other rows
Is it possible to solve this with only formulas, no coding?
data is organized in this way: B C D M1 K01 1 2 3 K02 1 2 3 M2 K07 1 2 3 In column A I need M1K011 M1K012 M1K013 M1K021 .... The answer is probably outthere somewhere, but I have no clue on how to look for it. Thanks in advance, Hans |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate cells, replacing blanks with cell contents from other rows
I assume column A is currently empty. Position the cursor in B2 then
press F5 (Goto), then click Special and Current Region. Then press F5 again, Special and Blanks. This should have highlighted all the cells which are blank in columns B and C, with B2 being the active cell. Then enter an equals sign, click on cell B1 then CTRL-ENTER, and all the blanks will be filled with a formula to take the value immediately above it. You can then enter: =B1&C1&D1 in cell A1 and copy this down for as much data as you have. Hope this helps. Pete On Feb 21, 9:02 am, wrote: Is it possible to solve this with only formulas, no coding? data is organized in this way: B C D M1 K01 1 2 3 K02 1 2 3 M2 K07 1 2 3 In column A I need M1K011 M1K012 M1K013 M1K021 ... The answer is probably outthere somewhere, but I have no clue on how to look for it. Thanks in advance, Hans |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate cells, replacing blanks with cell contents from other
The formula below placed in column A Give what you want. If you follow this
logic down column A then it should solve your problem =CONCATENATE(B1,C1,D1) =CONCATENATE(B1,C1,D2) =CONCATENATE(B1,C1,D3) A B C D M1K011 M1 K01 1 M1K012 2 M1K013 3 " wrote: Is it possible to solve this with only formulas, no coding? data is organized in this way: B C D M1 K01 1 2 3 K02 1 2 3 M2 K07 1 2 3 In column A I need M1K011 M1K012 M1K013 M1K021 .... The answer is probably outthere somewhere, but I have no clue on how to look for it. Thanks in advance, Hans |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate cells, replacing blanks with cell contents from other rows
On 21 feb, 10:34, "Pete_UK" wrote:
I assume column A is currently empty. Position the cursor in B2 then press F5 (Goto), then click Special and Current Region. Then press F5 again, Special and Blanks. This should have highlighted all the cells which are blank in columns B and C, with B2 being the active cell. Then enter an equals sign, click on cell B1 then CTRL-ENTER, and all the blanks will be filled with a formula to take the value immediately above it. You can then enter: =B1&C1&D1 in cell A1 and copy this down for as much data as you have. Hope this helps. Pete On Feb 21, 9:02 am, wrote: Is it possible to solve this with only formulas, no coding? data is organized in this way: B C D M1 K01 1 2 3 K02 1 2 3 M2 K07 1 2 3 In column A I need M1K011 M1K012 M1K013 M1K021 ... The answer is probably outthere somewhere, but I have no clue on how to look for it. Thanks in advance, Hans- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Thanks for the advise, The problem is that columns B to D are part of a pivot table, so I cannot fill them in that way. Regards, Hans |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate cells, replacing blanks with cell contents from other rows
On 21 feb, 10:34, "Pete_UK" wrote:
I assume column A is currently empty. Position the cursor in B2 then press F5 (Goto), then click Special and Current Region. Then press F5 again, Special and Blanks. This should have highlighted all the cells which are blank in columns B and C, with B2 being the active cell. Then enter an equals sign, click on cell B1 then CTRL-ENTER, and all the blanks will be filled with a formula to take the value immediately above it. You can then enter: =B1&C1&D1 in cell A1 and copy this down for as much data as you have. Hope this helps. Pete On Feb 21, 9:02 am, wrote: Is it possible to solve this with only formulas, no coding? data is organized in this way: B C D M1 K01 1 2 3 K02 1 2 3 M2 K07 1 2 3 In column A I need M1K011 M1K012 M1K013 M1K021 ... The answer is probably outthere somewhere, but I have no clue on how to look for it. Thanks in advance, Hans- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - I would like to have 1 formule I can fill down, maybe my examples wasn't the best, sometime A row is missing so we go from: M1K013 M1K022 Anyhow thanks for your help so far. Hans |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate cells, replacing blanks with cell contents from other rows
Try this method to fill all blank cells with values from above.
1.Select columns (B and C in the example) then Edit Goto Special Blanks. 2. press "=" and up arrow to refer to the cell above (=C1 in the example) followed by ctrl+enter to fill the range. 3. Now select columns and copy - paste spaceial values to remove formulas. On Feb 21, 9:02 am, wrote: Is it possible to solve this with only formulas, no coding? data is organized in this way: B C D M1 K01 1 2 3 K02 1 2 3 M2 K07 1 2 3 In column A I need M1K011 M1K012 M1K013 M1K021 ... The answer is probably outthere somewhere, but I have no clue on how to look for it. Thanks in advance, Hans |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate cells, replacing blanks with cell contents from other rows
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate cells, replacing blanks with cell contents from other rows
On 21 feb, 12:43, Ron Rosenfeld wrote:
On 21 Feb 2007 01:02:40 -0800, wrote: Is it possible to solve this with only formulas, no coding? data is organized in this way: B C D M1 K01 1 2 3 K02 1 2 3 M2 K07 1 2 3 In column A I need M1K011 M1K012 M1K013 M1K021 ... The answer is probably outthere somewhere, but I have no clue on how to look for it. Thanks in advance, Hans Try this formula to see if it does what you want with your data: =LOOKUP(2,1/($B$2:B2<""),$B$2:B2)& LOOKUP(2,1/($C$2:C2<""),$C$2:C2)& LOOKUP(2,1/($D$2:D2<""),$D$2:D2) Enter this in the first row of data, but adjust the range references to reflect that row. I assumed Row 2 in the above formula. Then copy/drag down as far as needed. The formula picks up the last entry in columns B,C and D up to the row in which the formula resides, and concatenates them. --ron- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Thanks heaps. That was exactly what I was looking for! Hans |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate cells, replacing blanks with cell contents from other
Hans,
I need to use two extra columns: in e2 put =if(b2="",e1,b2) in f2 put =if(c2="",f1,c2) in e1 put =b1 in f1 put =c1 in a1 put e1&f1&d1 then copy down then copy down -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. " wrote: Is it possible to solve this with only formulas, no coding? data is organized in this way: B C D M1 K01 1 2 3 K02 1 2 3 M2 K07 1 2 3 In column A I need M1K011 M1K012 M1K013 M1K021 .... The answer is probably outthere somewhere, but I have no clue on how to look for it. Thanks in advance, Hans |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate cells, replacing blanks with cell contents from other rows
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate cells, replacing blanks with cell contents from other
On 21 feb, 15:53, Martin Fishlock
wrote: Hans, I need to use two extra columns: in e2 put =if(b2="",e1,b2) in f2 put =if(c2="",f1,c2) in e1 put =b1 in f1 put =c1 in a1 put e1&f1&d1 then copy down then copy down -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. " wrote: Is it possible to solve this with only formulas, no coding? data is organized in this way: B C D M1 K01 1 2 3 K02 1 2 3 M2 K07 1 2 3 In column A I need M1K011 M1K012 M1K013 M1K021 .... The answer is probably outthere somewhere, but I have no clue on how to look for it. Thanks in advance, Hans- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Martin, I came up with the same solution, but please look at Ron's solution, no extra colums needed! Anyhow, thanks for your help. Hans |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate multiple rows and columns into 1 cell | Excel Discussion (Misc queries) | |||
copy range of cells with blanks then paste without blanks | Excel Worksheet Functions | |||
Stop rows from being deleted whilst allowing cells contents to cha | Excel Discussion (Misc queries) | |||
Concatenate and remove blanks | Excel Worksheet Functions | |||
Replacing Contents of 1 Cell to Another. | Excel Discussion (Misc queries) |