Combining Row Contents!

  • Thread starter Thread starter via135
  • Start date Start date
V

via135

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 row
B & C.

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

can anyone help me?

-via13
 
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.
 
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
 
Back
Top