combining text

[Jack]

Hi
I have some thing like this....
Date Amount Notes
1 Jan 25 rain
1 Jan 33 sunny
2 Jan 58
3 Jan 52 snow
3 Jan 103
etc

What I am looking to do is make a summary page that will some the amounts by
date (got that working no problem) and to combine the notes together so that
I would have something like below

Date Amount Notes
1 Jan 58 rain sunny
2 Jan 58
3 Jan 155 snow
etc


Thanks

 

[Dave Peterson]

Max. You've been playing with your date and forgot to change it back.

(or your time machine is working very nice...)

One try ..

Suppose this data-set is in Sheet1, A1:C6

Date Amount Notes
1 Jan 25 rain
1 Jan 33 sunny
2 Jan 58
3 Jan 52 snow
3 Jan 103

In Sheet2
------------
You have this set-up, where the dates are listed in A2 down

Date Amount Notes
1 Jan
2 Jan
3 Jan

Put in B2: =SUMIF(Sheet1!A:A,A2,Sheet1!B:B)

Select D2:H2
(i.e. a horizontal range** equal in size to
the # of rows of data in Sheet1)

Put in the formula bar:

=IF(TRANSPOSE(IF(Sheet1!$A$2:$A$6=A2,Sheet1!$C$2:$C$6,""))=0,"",TRANSPOSE(IF
(Sheet1!$A$2:$A$6=A2,Sheet1!$C$2:$C$6,"")))

Array enter the formula,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER

Put in C2: =TRIM(D2&" "&E2&" "&F2&" "&G2&" "&H2)

Select B2:H2, fill down

You'll get in A1:C3 :

Date Amount Notes
1 Jan 58 rain sunny
2 Jan 58
3 Jan 155 snow

(Hide away cols D to H, if desired)

**This condition will unfortunately, limit you
to cover up to a max of 253 rows of data in Sheet1,
[ Max cols is 256, less 3 cols used = 253 ]

Adapt / extend to suit
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Hi
I have some thing like this....
Date Amount Notes
1 Jan 25 rain
1 Jan 33 sunny
2 Jan 58
3 Jan 52 snow
3 Jan 103
etc

What I am looking to do is make a summary page that will some the amounts by
date (got that working no problem) and to combine the notes together so that
I would have something like below

Date Amount Notes
1 Jan 58 rain sunny
2 Jan 58
3 Jan 155 snow
etc


Thanks

 

[Max]

uurggh <g> sorry about that ..
(Desktop's harddisk reformatted recently)
Thanks for the alert, Dave !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Max. You've been playing with your date and forgot to change it back.

(or your time machine is working very nice...)

One try ..

Suppose this data-set is in Sheet1, A1:C6

Date Amount Notes
1 Jan 25 rain
1 Jan 33 sunny
2 Jan 58
3 Jan 52 snow
3 Jan 103

In Sheet2
------------
You have this set-up, where the dates are listed in A2 down

Date Amount Notes
1 Jan
2 Jan
3 Jan

Put in B2: =SUMIF(Sheet1!A:A,A2,Sheet1!B:B)

Select D2:H2
(i.e. a horizontal range** equal in size to
the # of rows of data in Sheet1)

Put in the formula bar:

=IF(TRANSPOSE(IF(Sheet1!$A$2:$A$6=A2,Sheet1!$C$2:$C$6,""))=0,"",TRANSPOSE(IF
(Sheet1!$A$2:$A$6=A2,Sheet1!$C$2:$C$6,"")))

Array enter the formula,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER

Put in C2: =TRIM(D2&" "&E2&" "&F2&" "&G2&" "&H2)

Select B2:H2, fill down

You'll get in A1:C3 :

Date Amount Notes
1 Jan 58 rain sunny
2 Jan 58
3 Jan 155 snow

(Hide away cols D to H, if desired)

**This condition will unfortunately, limit you
to cover up to a max of 253 rows of data in Sheet1,
[ Max cols is 256, less 3 cols used = 253 ]

Adapt / extend to suit
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Hi
I have some thing like this....
Date Amount Notes
1 Jan 25 rain
1 Jan 33 sunny
2 Jan 58
3 Jan 52 snow
3 Jan 103
etc

What I am looking to do is make a summary page that will some the

amounts
by

date (got that working no problem) and to combine the notes together

so
that

I would have something like below

Date Amount Notes
1 Jan 58 rain sunny
2 Jan 58
3 Jan 155 snow
etc


Thanks

 

[Max]

(Re-sent)

One try ..

Suppose this data-set is in Sheet1, A1:C6

Date Amount Notes
1 Jan 25 rain
1 Jan 33 sunny
2 Jan 58
3 Jan 52 snow
3 Jan 103

In Sheet2
------------
You have this set-up, where the dates are listed in A2 down

Date Amount Notes
1 Jan
2 Jan
3 Jan

Put in B2: =SUMIF(Sheet1!A:A,A2,Sheet1!B:B)

Select D2:H2
(i.e. a horizontal range** equal in size to
the # of rows of data in Sheet1)

Put in the formula bar:

=IF(TRANSPOSE(IF(Sheet1!$A$2:$A$6=A2,Sheet1!$C$2:$C$6,""))=0,"",TRANSPOSE(IF
(Sheet1!$A$2:$A$6=A2,Sheet1!$C$2:$C$6,"")))

Array enter the formula,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER

Put in C2: =TRIM(D2&" "&E2&" "&F2&" "&G2&" "&H2)

Select B2:H2, fill down

You'll get in A1:C3 :

Date Amount Notes
1 Jan 58 rain sunny
2 Jan 58
3 Jan 155 snow

(Hide away cols D to H, if desired)

**This condition will unfortunately, limit you
to cover up to a max of 253 rows of data in Sheet1,
[ Max cols is 256, less 3 cols used = 253 ]

Adapt / extend to suit

 

[Jack]

Works great but I have 800 lines

Any ideas?

One try ..

Suppose this data-set is in Sheet1, A1:C6

Date Amount Notes
1 Jan 25 rain
1 Jan 33 sunny
2 Jan 58
3 Jan 52 snow
3 Jan 103

In Sheet2
------------
You have this set-up, where the dates are listed in A2 down

Date Amount Notes
1 Jan
2 Jan
3 Jan

Put in B2: =SUMIF(Sheet1!A:A,A2,Sheet1!B:B)

Select D2:H2
(i.e. a horizontal range** equal in size to
the # of rows of data in Sheet1)

Put in the formula bar:

=IF(TRANSPOSE(IF(Sheet1!$A$2:$A$6=A2,Sheet1!$C$2:$C$6,""))=0,"",TRANSPOSE(IF
(Sheet1!$A$2:$A$6=A2,Sheet1!$C$2:$C$6,"")))

Array enter the formula,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER

Put in C2: =TRIM(D2&" "&E2&" "&F2&" "&G2&" "&H2)

Select B2:H2, fill down

You'll get in A1:C3 :

Date Amount Notes
1 Jan 58 rain sunny
2 Jan 58
3 Jan 155 snow

(Hide away cols D to H, if desired)

**This condition will unfortunately, limit you
to cover up to a max of 253 rows of data in Sheet1,
[ Max cols is 256, less 3 cols used = 253 ]

Adapt / extend to suit
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Hi
I have some thing like this....
Date Amount Notes
1 Jan 25 rain
1 Jan 33 sunny
2 Jan 58
3 Jan 52 snow
3 Jan 103
etc

What I am looking to do is make a summary page that will some the amounts by
date (got that working no problem) and to combine the notes together so that
I would have something like below

Date Amount Notes
1 Jan 58 rain sunny
2 Jan 58
3 Jan 155 snow
etc


Thanks

 

[Max]

Works great but I have 800 lines
Any ideas?

Is this "800 lines" data for the whole year <g> ?

The SUMIF for the source col B is not a problem, we could just copy straight
down. It's the text manipulation for the source col C which is tricky.

If the 800 lines is data for the whole year, one way is just to manually cut
up the 800 lines into roughly 4 sheets of 200 lines each (a
"quarter-by-quarter" approach?), taking care to ensure that the all the
dates in col A in any one sheet are "complete", i.e. all source lines with
duplicate dates are housed together in the same sheet.

Then the suggested method could be applied to each of the 4 source sheets
and the details pulled correspondingly into another 4 extract sheets.

And finally, the stuff from the 4 extract sheets pieced together
chronologically with a sequential copy > paste special as Values (& as
Formats) from each of the 4 extract sheets into a summary sheet.

It's a little manual, but not impossible to implement ..

Do hang around. Perhaps others would step in and offer you alternative /
better insights - maybe a vba approach, or other worksheet function
alternatives ?

Good luck !