# Combine multiple rows data in to one row

1. ## Combine multiple rows data in to one row

Hi,

Need to club multiple rows text data in to one row.

An excel file attached with necessary details.

Pl. help.

Thanks,
Nagesh.  Register To Reply

2. ## Re: Combine multiple rows data in to one row

If you mean it that you're using Excel 2007, this requires either a user-defined function implemented in VBA for concatenation, or supporting cells to the right of your original data. I'll take the 2nd approach. I'll also rename the worksheet containing your data as Data. I'm also assuming your data is sorted on column B in ascending order then on column G in descending order (YES followed by NO for the same job). Add formulas there.

I2: =IF(OR(B2<>B3,G2<>G3),1)
J2: =C2&"/"&D2

I3: =IF(OR(B3<>B4,G3<>G4),MAX(I\$2:I2)+1)
J3: =IF(I2,"",J2&",")&C3&"/"&D3

Select I3:J3 and fill down to the last row of data.

Summarize in another worksheet with labels in row 1 beginning in cell A1.

A2: =Data!B2
B2: =SUMPRODUCT(Data!E\$2:E\$1000,(Data!B\$2:B\$1000=\$A2)*(Data!G\$2:G\$1000=\$D2))
C2: =LOOKUP(A2,Data!B\$2:F\$1000)
D2: YES
E2: =VLOOKUP(ROWS(E\$2:E2),Data!\$I\$2:\$J\$1000,2,0)

A3: =IF(D2="YES",A2,INDEX(Data!B\$2:B\$1000,COUNTIF(Data!B\$2:B\$1000,"<="&A2)+1))
B3: =SUMPRODUCT(Data!E\$2:E\$1000,(Data!B\$2:B\$1000=\$A3)*(Data!G\$2:G\$1000=\$D3))
C3: =IF(D2="YES",C2,LOOKUP(A3,Data!B\$2:F\$1000))
D3: =IF(D2="YES","NO","YES")
E3: =VLOOKUP(ROWS(E\$2:E3),Data!\$I\$2:\$J\$1000,2,0)

Select A3:E3 and fill down as far as needed. The column A to C formulas will return #REF! errors when the data has been exhausted.  Register To Reply

3. ## Re: Combine multiple rows data in to one row

Hi hrlngrv,

Thank you for your time and effort. Your solution is working to some extent. My data is not sorted. and the actual requirement and output is attached. Appreciate if i can get solution.

Thanks,
Nagesh.  Register To Reply

4. ## Re: Combine multiple rows data in to one row

This proposal is based on the layout shown in the file attached to post #3 and employs 7 helper columns.
Column J is populated using: =LEFT(F2,1)+0
Column K is populated using: =IF(J2>2,0,COUNTIFS(A\$2:A2,A2,B\$2:B2,B2,J\$2:J2,"<=2"))
Column L is populated using: =IF(K2=0,"",IF(K2=1,B2&"/"&C2,L1&","&C2))
Column M is populated using: =K2>=K3
Columns N:P are similarly populated to columns K:M.
Columns G:H are populated using array entered formulas** similar to: =IF(LEFT(F2,1)+0>2,"",INDEX(L\$2:L\$12,LARGE(IF(((A\$2:A\$12=A2)*(B\$2:B\$12=B2)*(M\$2:M\$12=TRUE)),ROW(L\$2:L\$12)-ROW(L\$1)),1)))
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.  Register To Reply

5. ## Re: Combine multiple rows data in to one row

Hi JeteMc,

Thank you for your time and efforts.

My observations are given below:

1. G8, G9 and G11 should display as A1/1,2,D1/2 (Since all of them comes under CLEARED category)
2. H10 & H11 should display as A1/3,D2/1 (Since both are UNCLEARED category)
3. Noticed that the formula is not working when I tried to change F3 to "1 Cleared"
Similarly when I change F8 to "3 Insp. To Be Cleared", the formula is not working.

Grateful if you can fix these so that the formulas work perfectly.

Thanks,
Nagesh.  Register To Reply

6. ## Re: Combine multiple rows data in to one row

Are you still using XL 2007. If you are using a later version, please advise and update your profile to reflect the correct version. Responses are based upon the specific excel version you are using.  Register To Reply

7. ## Re: Combine multiple rows data in to one row

Hi Alan,

Am using Office 2013 version. dont know how to update the version. Will try and change.

Thanks,
Nagesh.  Register To Reply

8. ## Re: Combine multiple rows data in to one row

Updated my profile.

Thanks,
Nagesh.  Register To Reply

9. ## Re: Combine multiple rows data in to one row

The formula in column L has been modified to read:
Formula:  `Please Login or Register  to view this content.`

A similar modification has been made to the formula in column O.
Let us know if you have any questions.  Register To Reply

10. ## Re: Combine multiple rows data in to one row

Hi JeteMc,

Thank you once again.

Little more modification required. Excel file attached indicating how the result should look like.

Pl. help.

Thank you,
Nagesh.  Register To Reply

11. ## Re: Combine multiple rows data in to one row

This proposal adds two more helper columns per category, Cleared and Uncleared, to what was shown previously.
The first column is populated using: =IF(OR(K2=0,M2=FALSE),0,COUNTIFS(A\$2:A2,A2,M\$2:M2,TRUE))
The second column is populated using: =IF(K2=0,"",IF(N2=1,L2,INDEX(O\$1:O1,AGGREGATE(14,6,ROW(O\$1:O1)/((A\$1:A1=A2)*(O\$1:O1<>"")),1))&","&IF(COUNTIFS(A\$2:A2,A2,B\$2:B2,B2,J\$2:J2,"<=2")=1,L2,C2)))
Let us know if you have any questions.  Register To Reply

12. ## Re: Combine multiple rows data in to one row

Hi JeteMc,

Its almost working great. Noticed an small issue. When I Changed F2 to 1 Cleared and keep F3 Blank, A1/1 is not displaying in G2,G5,G6,G7.

Excel file attached with details.

Pl. do the needful.

Thanks,
Nagesh.  Register To Reply

13. ## Re: Combine multiple rows data in to one row

1. In cell G2 (and then copy down): =IF(F2="","",IF(LEFT(F2,1)+0>2,"",INDEX(O\$2:O\$12,AGGREGATE(14,6,(ROW(L\$2:L\$12)-ROW(L\$1))/((A\$2:A\$12=A2)*(M\$2:M\$12=TRUE)),1))))
2. In cell H2 (and then copy down): =IF(F2="","",IF(LEFT(F2,1)+0<3,"",INDEX(T\$2:T\$12,AGGREGATE(14,6,(ROW(T\$2:T\$12)-ROW(T\$1))/((A\$2:A\$12=A2)*(R\$2:R\$12=TRUE)),1))))
3. In cell J2 (and then copy down): =IF(F2="","",LEFT(F2,1)+0)
Let us know if you have any questions.  Register To Reply

14. ## Re: Combine multiple rows data in to one row

Hi JeteMc,

Incorporated the formulas in my original file. A very small modification in one of the formulas (which shows error).

Excel file attached highlighting the issue.

Pl. do the needful.

Thanks,
Nagesh.  Register To Reply

15. ## Re: Combine multiple rows data in to one row

Is this what you want?

=AND(P3<=P4,Q3<>"")

This is what you seem to be describing in your note.  Register To Reply

16. ## Re: Combine multiple rows data in to one row

My proposed solution was based on the file attached to post #1 which was sorted by both Job and Building.
If the file attached to post #14 is sorted (using Sort on Data tab) first by Job and then by Building, then the values in the Clearance 3 and Clearance 4 columns are correct.
Let us know if you have any questions.  Register To Reply

17. ## Re: Combine multiple rows data in to one row

Hi JeteMc,

My data is not sorted. It is sorted with some other columns, where Job and Building cannot be sorted. Since i dont want to confuse with huge data, i put sample data with only two job numbers.

Pl. do the needful. Your entire solution is excellent only this small correction is required.

Pl. help.

Thanks,
Nagesh.  Register To Reply

18. ## Re: Combine multiple rows data in to one row

This is more than a "small correction" however it seems to work.
The formula for columns R and V is changed to read:
Formula:  `Please Login or Register  to view this content.`

The formula for columns S and W is changed to read:
Formula:  `Please Login or Register  to view this content.`

Let us know if you have any questions.  Register To Reply

19. ## Re: Combine multiple rows data in to one row

Hi JeteMc,

This is working well with Cleared column but few cells show some error.

Excel file attached with necessary info.

Pl. help.

Sorry to put you in trouble again and again. This is final please.

Thanks,
Nagesh.  Register To Reply

20. ## Re: Combine multiple rows data in to one row

Try the following modification to the formula in DX2 and then copy down:
Formula:  `Please Login or Register  to view this content.`

Let us know if you have any questions.  Register To Reply

21. ## Re: Combine multiple rows data in to one row

Hi JeteMc,

The formulas are working absolutely perfect.

Thank you very much for your time and effort.

It really helped me on my report where so much manual entry is avoided.

Once again thanks a Ton.

Nagesh.  Register To Reply

22. ## Re: Combine multiple rows data in to one row

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.  Register To Reply