Thread
:
Concatenate cells, replacing blanks with cell contents from other rows
View Single Post
#
7
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
Posts: 5,651
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
Reply With Quote
Ron Rosenfeld
View Public Profile
Find all posts by Ron Rosenfeld