Combining Cells

[jraymond1984]

Hi

Can someone please help me write some code to merge cells with input as follows:

Customer ID Comment Date Comment
22 1/1/11 A
23 2/2/11 B
23 5/7/11 C
23 6/9/11 D
24 8/8/11 E
25 17/4/11 F

I would like the output to occur in another sheet as follows please:
Customer ID Comment
22 1/1/11 - A
23 2/2/11 - B
5/7/11 - C
6/9/11 - D
24 8/8/11 - E
25 17/4/11 - F

Thanks in advance for anyone who is able to write this code!!!

 

[cgcamal]

Hi jraymond1984,

Try with:

Excel Workbook
	A	B	C
1	Customer ID	Comment Date	Comment
2	22	01/01/2011	A
3	23	02/02/2011	B
4	23	05/07/2011	C
5	23	06/09/2011	D
6	24	08/08/2011	E
7	25	17/04/2011	F
Sheet1

Excel Workbook
	A	B	C
1	Customer ID	Comment Date	Comment
2	22	01/01/2011	A
3	23	02/02/2011	B
4		05/07/2011	C
5		06/09/2011	D
6	24	08/08/2011	E
7	25	17/04/2011	F
Sheet2

Copy in these Cells and copy down:

Cell Formulas
Range	Formula
A1	=Sheet1!A1
B1	=Sheet1!B1
C1	=Sheet1!C1

Within Sheet2, select range A2:A7 and introduce the formula below, then enter it with Ctrl+Shift+Enter:

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

Regards

 

[Peter_SSs]

Welcome to the MrExcel board!

Are you saying you want all the "23" dates and comments merged into a single cell like this? If not, please try to give more detail.

Excel Workbook
	A	B
1	Customer ID	Comment
2	22	1/1/11 - A
3	23	2/2/11 - B5/7/11 - C6/9/11 - D
4	24	8/8/11 - E
5	25	17/4/11 - F
6
Merge Data

 

[jraymond1984]

Yes Peter, that is exactly my query.

Many thanks in advance,

Jonathan

 

[Peter_SSs]

Jonathan

Test this in a copy of your workbook.

It assumes ..

1. Certain sheet names - change in the code to match yours.

2. That the 'Result' sheet already exists but has no data in it. If this is not the case and you need help to deal with that post back with more details.

3. Existing data starts in cell A1 of the 'Original' sheet.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Combine_Data()<br>    <SPAN style="color:#00007F">Dim</SPAN> aData, aRes<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, L <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Cntr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Original")         <SPAN style="color:#007F00">'<-- Change to suit ************</SPAN><br>        aData = .Range("A1", .Range("C" & .Rows.Count).End(xlUp)).Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    L = <SPAN style="color:#00007F">UBound</SPAN>(aData, 1)<br>    <SPAN style="color:#00007F">ReDim</SPAN> aRes(1 <SPAN style="color:#00007F">To</SPAN> L, 1 <SPAN style="color:#00007F">To</SPAN> 2)<br>    aRes(1, 1) = aData(1, 1)<br>    aRes(1, 2) = aData(1, 3)<br>    Cntr = 1<br>    <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> L<br>        <SPAN style="color:#00007F">If</SPAN> aData(i, 1) = aData(i - 1, 1) <SPAN style="color:#00007F">Then</SPAN><br>            aRes(Cntr, 2) = aRes(Cntr, 2) & Chr(10) & _<br>                aData(i, 2) & " - " & aData(i, 3)<br>        <SPAN style="color:#00007F">Else</SPAN><br>            Cntr = Cntr + 1<br>            aRes(Cntr, 1) = aData(i, 1)<br>            aRes(Cntr, 2) = aData(i, 2) & " - " & aData(i, 3)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Result")           <SPAN style="color:#007F00">'<-- Change to suit ************</SPAN><br>        .Columns("B").ColumnWidth = 50<br>        .Range("A1").Resize(L, 2).Value = aRes<br>        .Columns("A:B").AutoFit<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[jraymond1984]

Thank you very much Peter for your prompt response.

The "Results" sheet already has data in it, so the code needs to match the customer number (23 in this case has a few different data sets) and bring in all the data from the original sheet.

Many thanks once again for all your assistance.

Regards,

Jonathan

 

[Peter_SSs]

Thank you very much Peter for your prompt response.

The "Results" sheet already has data in it, so the code needs to match the customer number (23 in this case has a few different data sets) and bring in all the data from the original sheet.

Many thanks once again for all your assistance.

Regards,

Jonathan

Kind of some important additional information omitted from the original question.

Still not clear. What data is in the Results sheet?

- Just a list of customer numbers? All posible customer numbers? That is, if I look for 23 will I be sure to find it already in the Results sheet or might it have to be added to the existing list?

- Customer numbers and some 'Date - Comment' entries?
If so should the new 'Date - Comment' data be appended to existing data or replace it?

- What exactly do you mean by ".. has a few different data sets"?

Just remember we haven't seen the workbook so tell us as much as you can about what your have, where, and what you are trying to achieve.

 

[jraymond1984]

Hi Peter

Apologies if I was unclear - I am new to VBA.

The data that is in the Results sheet contains all possible customer ID numbers. So, if you look for 23 in the Results sheet, you will find that one and only one row has customer 23 in it. However, if you look for customer 23 in the Original sheet, you will find there is more than one row which has customer 23 in it.

Both the Original sheet and the Results sheet have more information as well, though I do not think this has any bearing on the code.
For your reference, the additional information in the Results sheet are: value of debt, amount collected, amount outstanding, debtor open/closed, reopen file (on a date), additional comments.
The information that is contained in the Original sheet is: Customer ID, Comment date, Comment

Regarding your query about:
- Customer numbers and some 'Date - Comment' entries?
If so should the new 'Date - Comment' data be appended to existing data or replace it?
The way I have my data organised is that in the Original sheet, as discussed above, the columns I have are Customer ID, Comment date, Comment. I add rows to the data as more information comes through (i.e. there will be numerous entries for customer 23, but they will be randomly dispersed amongst other customers)(and also this means that the newest comment is added to the bottom of the data). So, if on 28/4/11 I input something for customer 23, and I also input something for customer 87, I will have two rows. I can also on 30/4/11 input some more data for customer 23, however this will be in a new row. So to answer your question above, it does not replace the data, but adds to it. This also should answer your question regarding what it is I mean about "...has a few different data sets" i.e. customer 23 will have different comments being inputted at different dates.

What I am aiming to achieve is for the Results sheet to look at what customer number I have in A4, then A5, then A6 all the way down to A99999. (NB: there will only be one unique Customer in A4:A99999 of the Results sheet). Then, once the computer has seen what customer number is in A4 of the Results sheet, I want the computer to look in cells A4:A99999 of the Original sheet and then bring back all the merged data and put it in cell M4 of the Results sheet.
For instance, if cell A4 of the Results sheet has Customer 23, I want the computer to look in cells A4:A99999 of the Original sheet and see when there is a Customer 23. If for instance:
cell A8 has Customer 23, cell B8 has 5/5/11, cell C8 has Comment 1
cell A24 has Customer 23, cell B24 has 7/7/11, cell C24 has Comment 2
cell A37 has Customer 23, cell B37 has 9/9/11, cell C37 has Comment 3
then I would like the computer to put in cell M4 of the Results sheet:
5/5/11 - Comment 1
7/7/11 - Comment 2
9/9/11 - Comment 3

I hope this is all clear now and I apologise for not being clearer at first instance.

Once again, I really appreciate the assistance you are providing!

Regards,

Jonathan

 

[Peter_SSs]

The data that is in the Results sheet contains all possible customer ID numbers. So, if you look for 23 in the Results sheet, you will find that one and only one row has customer 23 in it.

OK, that is nice & clear.

in the Original sheet, ... I add rows to the data as more information comes through (i.e. there will be numerous entries for customer 23, but they will be randomly dispersed amongst other customers)(and also this means that the newest comment is added to the bottom of the data). So, if on 28/4/11 I input something for customer 23, and I also input something for customer 87, I will have two rows. I can also on 30/4/11 input some more data for customer 23, however this will be in a new row. So to answer your question above, it does not replace the data, but adds to it.

Perhaps I still haven't really understood this, but if you are gradually adding more rows to 'Original' and appending to data already in 'Result', then when the code is run how would the code determine which data/rows have already been transferred to 'Result' and which have not?

..bring back all the merged data and put it in cell M4 of the Results sheet..

And I think you are saying that there could already be some data in M4?


Could be more questions later.

 

[jraymond1984]

Hi Peter

What would happen is the code should run through all the data in Original sheet and if there has been an additional row added in the Originsl sheet relating to customer 23, then there will be one additional line of text within cell M4 of the Results sheet. I do not think it should matter if when you run the macro there is an addition to a cell in columnn M of the Results sheet - I thinknthe macro should be run each time as if column M of the Results sheet is blank.

To answer your second question, yes, there will (or may) already be data in cell M4 of the Results sheet.

Once again, many many thanks for spending the time on this.

Regards,

Jonathan