+ Reply to Thread
Results 1 to 22 of 22

Combine multiple rows data in to one row

  1. #1
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    248

    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.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    484

    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.

  3. #3
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    248

    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.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,126

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    248

    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.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    17,932

    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.

  7. #7
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    248

    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.

  8. #8
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    248

    Re: Combine multiple rows data in to one row

    Updated my profile.

    Thanks,
    Nagesh.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,126

    Re: Combine multiple rows data in to one row

    The formula in column L has been modified to read:
    Formula: copy to clipboard
    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.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    248

    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.
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,126

    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.
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    248

    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.
    Attached Files Attached Files

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,126

    Re: Combine multiple rows data in to one row

    Please make the following changes:
    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.

  14. #14
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    248

    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.
    Attached Files Attached Files

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,644

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  16. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,126

    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.

  17. #17
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    248

    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.

  18. #18
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,126

    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: copy to clipboard
    Please Login or Register  to view this content.

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

    Let us know if you have any questions.

  19. #19
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    248

    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.

  20. #20
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,126

    Re: Combine multiple rows data in to one row

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

    Let us know if you have any questions.

  21. #21
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    248

    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.

  22. #22
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,126

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Combine multiple rows of data into 1 row with coluimns
    By sethboyardee in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2016, 09:07 AM
  2. Combine Data in Multiple Rows into Single Row
    By koonwei605 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-18-2014, 07:43 AM
  3. [SOLVED] LF code to sum multiple rows of data and combine into one row
    By Granite-Granny in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-20-2014, 02:34 AM
  4. Replies: 6
    Last Post: 02-19-2014, 10:25 AM
  5. Macro to combine data from multiple rows
    By mummafrog in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-03-2012, 10:21 PM
  6. Trying to combine multiple rows of data into one
    By julhennessy in forum Excel General
    Replies: 6
    Last Post: 09-17-2012, 02:40 PM
  7. Combine Data from Multiple Rows
    By rhudgins in forum Excel General
    Replies: 3
    Last Post: 10-07-2011, 10:38 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1