Concatenate cells, replacing blanks with cell contents from other rows

H

hans.wittoeck

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
 
P

Pete_UK

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
 
M

Mike

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
 
H

hans.wittoeck

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





- Tekst uit oorspronkelijk bericht weergeven -
Click to expand...

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
 
H

hans.wittoeck

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





- Tekst uit oorspronkelijk bericht weergeven -
Click to expand...

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
 
L

Lori

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.
 
R

Ron Rosenfeld

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
Click to expand...


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
 
H

hans.wittoeck

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 -
Click to expand...

Thanks heaps. That was exactly what I was looking for!

Hans
 
M

Martin Fishlock

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
 
H

hans.wittoeck

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.







- Tekst uit oorspronkelijk bericht weergeven -
Click to expand...

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
 
Top