hi all!

is there any way to combine the data in two cells (row-wise) in xl?

for example
A1 - date; A2 - narration (text); A3 - amount
B1 - blank; B2 - narration (text); B3 - blank
C1 -blank; C2 - narration (text); C3 - blank

now i want

A1 - date; A2 - combined text of A2,B2&C2; A3 - amount

after converting the three records into a single one, i don't need rows
B & C.

this is required simply because to reduce the records and make the
worksheet size to 1/3.

can anyone help me?

-via135


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=502583

Via -
It is a multi-step process. Start with a helper column, say column D.
In D1, enter something like one of the following:

= A2 & " " & B2 & " " & C2 which will put a space between each of the
entries. You could put any other text between the quotes such as
semi-colon or dash.

If you want the result to look like a bulleted list, you could try the
following...
=CHAR(149) & " " &A2 & CHAR(10) & CHAR(149) & " " & B2 & CHAR(10) &
CHAR(149)& " " & C2

This will make a bullet character at the start of each line and a
carriage return after each line.

Copy the formula down for all of column D then convert to values by
highlighting the column, choosing Edit Copy then Edit Paste Special
Values OK then escape.

Then sort A1 through the bottom row of data in column D by column A.
This will put all rows with a date at the top. All other rows can be
deleted.

- John Michl

sorry..i have completely messed up the cell ref in my example.
the right one is us under:

A1 - date; B1 - narration (text); C1 - amount
A2 - blank; B2 - narration (text); C2 - blank
A3 - blank; B3 - narration (text); C3 - blank

now i want

A1 - date; B1 - combined text of B1,B2 & B3; C1 - amount;

after converting the three records into a single one, i don't need rows
2 & 3.


via135 Wrote:
hi all!

is there any way to combine the data in two cells (row-wise) in xl?

for example
A1 - date; A2 - narration (text); A3 - amount
B1 - blank; B2 - narration (text); B3 - blank
C1 -blank; C2 - narration (text); C3 - blank

now i want

A1 - date; A2 - combined text of A2,B2&C2; A3 - amount

after converting the three records into a single one, i don't need rows
B & C.

this is required simply because to reduce the records and make the
worksheet size to 1/3.

can anyone help me?

-via135


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=502583

Same concepts from my earlier post apply. Combine, B1, B2, B3 in a
formula in cell D1. Copy that formula to the bottom of the sheet.
Change to values. The resort the entire sheet. The extra rows will
move to the bottom so you can delete them.

- John

thks..John!

this is what i exactly wanted!!!

-via135


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=502583