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

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

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

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

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

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

Concatenate cells, replacing blanks with cell contents from other rows
 

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

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

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

Concatenate cells, replacing blanks with cell contents from other rows
 

On 21 Feb 2007 05:41:28 -0800, wrote:

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

Hans


You're welcome. Glad to help. Thanks for the feedback.
--ron

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