Concatenate cells, replacing blanks with cell contents from other rows
hans.w...@gmail.com
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
Pete_UK
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
Mike
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
"hans.w...@gmail.com" 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
> ....
hans.w...@gmail.com
>
> - 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
hans.w...@gmail.com
>
> - 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
Lori
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, hans.witto...@gmail.com wrote:
Ron Rosenfeld
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
hans.w...@gmail.com
>
> - Tekst uit oorspronkelijk bericht weergeven -
Thanks heaps. That was exactly what I was looking for!
Hans
Martin Fishlock
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.
"hans.w...@gmail.com" 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
> ....
Ron Rosenfeld
>Thanks heaps. That was exactly what I was looking for!
>
>Hans
You're welcome. Glad to help. Thanks for the feedback.
--ron
hans.w...@gmail.com
>
> - 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