+ Reply to Thread
Results 1 to 66 of 66

Combine multiple rows data in to one row

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

    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
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    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 2019
    Posts
    425

    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 2019
    Posts
    17,428

    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 2019
    Posts
    425

    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, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    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.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

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

    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 2019
    Posts
    425

    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 2019
    Posts
    17,428

    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 2019
    Posts
    425

    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 2019
    Posts
    17,428

    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 2019
    Posts
    425

    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 2019
    Posts
    17,428

    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 2019
    Posts
    425

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,392

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  16. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    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 2019
    Posts
    425

    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 2019
    Posts
    17,428

    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 2019
    Posts
    425

    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 2019
    Posts
    17,428

    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 2019
    Posts
    425

    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 2019
    Posts
    17,428

    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.

  23. #23
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi JetMc,

    Noticed an error while working with the formula.

    Attached sample excel file with necessary details.

    Pl. help.

    Thanks,
    Nagesh.
    Attached Files Attached Files

  24. #24
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combine multiple rows data in to one row

    Will be away from my computer for a couple of days but will look when I get back.

  25. #25
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Ok.

    Pl. do the needful once you are back.

    Thanks,
    Nagesh.

  26. #26
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combine multiple rows data in to one row

    The formula in cells BP3 and down can be changed to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The formula in cells BT3 and down can be changed to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  27. #27
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi JeteMc,

    Need little correction in the formula.

    Excel file attached with necessary details.

    Pl. help.

    Thanks,
    Nagesh.
    Attached Files Attached Files

  28. #28
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combine multiple rows data in to one row

    The following would be used instead of the current formula in column BN (which may eliminate the need for columns BO:BP):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  29. #29
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi JeteMc,

    After applying this formula, result appearing multiple times.

    For your ready reference attached the file and inserted new columns indicating the desired result.

    Pl. do the needful.

    Thanks,
    Nagesh.
    Attached Files Attached Files

  30. #30
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combine multiple rows data in to one row

    Paste the following into cell BN3 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Paste the following into cell BP3 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I'll work on matching column BV later and once I have received confirmation that the above formulas work as expected.
    Let us know if you have any questions.

  31. #31
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi JeteMc,

    Applied your formulas in BN3 and BP3 and copied down. But the results are not as per the requirement. Attached the file after applying the formulas and desired result.

    Pl. do the needful.

    Thanks,
    Nagesh.
    Attached Files Attached Files

  32. #32
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combine multiple rows data in to one row

    I changed BN3 and down to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files

  33. #33
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi JeteMc,

    Almost OK but some places i am getting #NUM!

    Pl. find attached the file.

    Once done pl. put formula in BR and BT also.

    Pl. do the needful.

    Thanks,
    Nagesh.
    Attached Files Attached Files

  34. #34
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combine multiple rows data in to one row

    Try pasting the following into cell BN3 and double click the fill handle to copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I'll work on columns BR and BT once we know that this one works correctly.
    Let us know if you have any questions.

  35. #35
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi JeteMc,

    Its working for BN and BP columns.

    If you remember, you put formula in Col.S based on BN and BP. When "9 Plan B" comes in last rows, its not working.

    Attached the file for your ready reference.

    Pl. help.

    Thanks,
    Nagesh.
    Attached Files Attached Files

  36. #36
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combine multiple rows data in to one row

    Paste the following into cell BO2 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  37. #37
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi JeteMc,

    Its working perfectly.

    First of all i salute your patience. In order to get the desired outcome / result i kept on troubling you but you never said no. Anyone in your place would have kept quite without answering my queries / working on the solved thread. That shows your character. You are really great. I am really grateful to you.

    Pl. put formula for BR, BT and T columns.

    Thank you once again,
    Nagesh.

  38. #38
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combine multiple rows data in to one row

    Appreciate the kind words.
    I will be away from my computer for a few days, but will get back to work on this thread when I return unless someone has finished it beforehand.
    Please indicate the corrections needed in columns T, BR and BT as per the file attached to post #35.
    I hope that you have a blessed day.

  39. #39
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi JeteMc,

    Pl. find attached the excel file by highlighting the requirement at Col.T, BR & BT.

    Pl. do the needful.

    Thanks,
    Nagesh.
    Attached Files Attached Files

  40. #40
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combine multiple rows data in to one row

    Please explain why BR86 and BR90 should be blank.

  41. #41
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Since K6 and K9 doesnot beging with 3 to 6.

    ex: BR85 is A1/2 because K85 begin with 6.

  42. #42
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combine multiple rows data in to one row

    This seems to have solved the issue for column BR and BT.
    Change the formula in BR3 and down so that it reads:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Also try the following in T3 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  43. #43
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi JeteMc,

    Its working with the data available. When i tried to add a row (Row No.320), it shows error in Col.T and Col.BT.

    My actual requirement is a pivot table (pivot table sheet added), which show error.

    Excel file attached for your ready reference highlighting the error.

    Pl. do the needful.

    Thanks,
    Nagesh.
    Attached Files Attached Files

  44. #44
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,392

    Re: Combine multiple rows data in to one row

    This thread is marked as SOLVED!!!

  45. #45
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combine multiple rows data in to one row

    Try changing the formula in BT2 and down so that it reads:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Similarly the formula in cells BP2 and down could be changed to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The above seems to have corrected the problem in cell AD75 on the pivot table.
    Please address AliGW's statement by removing the [SOLVED] designation using the Thread Tools menu above your first post.
    Let us know if you have any questions.

  46. #46
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi JeteMc,

    Its working fine now. Thanks a lot for your time and efforts on this issue.

    Yes, the pivot table too corrected, as the AC and AD columns of Pivot table are arrived from BP and BT respectively of Status sheet.

    As soon as i saw AliGW's post, i changed the status to Unsolved. Now will put back to Solved.

    I troubled you a lot on this. Thank you for your patience and support.

    Nagesh.

  47. #47
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combine multiple rows data in to one row

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved' (again). I hope that you have a blessed day.

  48. #48
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi JeteMc,

    Noticed an issue with the formula you provided.

    An excel file attached with necessary example.

    Pl. help.

    Thanks,
    Nagesh.
    Attached Files Attached Files

  49. #49
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,278

    Re: Combine multiple rows data in to one row

    Power Query
    Please Login or Register  to view this content.
    Attached Files Attached Files

  50. #50
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Combine multiple rows data in to one row

    Please Login or Register  to view this content.
    Attached Files Attached Files
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  51. #51
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi Czeslaw and John Topley,

    Thanks for your time.

    @JeteMc has worked a lot on this thread and provided solution and was working well. Noticed small issues and the same are indicated in latest post (Post No.48).

    Pl. help.

    Thanks,
    Nagesh.

  52. #52
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combine multiple rows data in to one row

    Modify the formula in cells DU3 and down to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    That said if there is a way to accomplish this using either Power Query or VBA it will probably be more resource efficient.
    Let us know if you have any questions.
    Attached Files Attached Files

  53. #53
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi JeteMc,

    The modified one is working fine. Thank you.

    You are always so supportive and helpful in providing solution even though I trouble you again and again.

    As suggested, if this can be done through VBA code, that is more beneficial. I don't know VBA coding. Grateful if you can help me on VBA code.

    Thanks,
    Nagesh.

  54. #54
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi John Topley,

    You already put efforts to write code, but the code written for the first post. Latest post is post no.52. If the code can be modified modified as per post no.52, that will serve the purpose.

    This is just optonal only.

    Currently the purpose is served with the formula provided by JeteMc in post No.52.

    Thanks,
    Nagesh.

  55. #55
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Combine multiple rows data in to one row

    As is your way, the column headings in file in post#52 are diffrent to those in your test file so please "map" the old with the new.

    No "Customer" data ??

  56. #56
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi John Topley,

    The test file logic was very basic later changed entirely. Hence test file will not suit to the present requirement.

    No problem. I will manage with the formulas. If at all i face any issues in future, will post for VBA code.

    Thank you.

  57. #57
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Combine multiple rows data in to one row

    THank you. After all the effort put in by JeteMC it is only fair to use his formula: I'll put VBA on standby!!!

  58. #58
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi JeteMc,

    The formula working perfectly as required. Noticed that if Column M contains a formula (trying to get the data from other source), unable to get the desired result.

    Grateful if you can look in to it.

    Moreover, will there be a way to restrict the formula to the Table range. Current formula contains range outside Table. If the range exceeds and sometimes by overlook, the result giving errors. But this is not affecting the overall result as such. Will be more robust if the formula is stricted to the Table.

    For your ready reference, attached the file.

    Thanks in advance,
    Nagesh.
    Attached Files Attached Files

  59. #59
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combine multiple rows data in to one row

    Using structured references for the formula in the phases cleared column should restrict it to the table range:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Looking at the other formulas it appears the some already have structured references, do you want us to work on those also?

  60. #60
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi JeteMC,

    Tried applying structured references on my own but could not do it.

    Grateful if you can extend the formula to Col. 160 to 168.

    Moreover, if Col.M contains linked data, formula not working for some or all.

    Pl. apply formulas to attached file so that I may not disturb you on this again and again.

    Thanks,
    Nagesh.
    Attached Files Attached Files

  61. #61
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combine multiple rows data in to one row

    Changing the formula in the Phases Cleared column seems to work:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files

  62. #62
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi JeteMc,

    Thanks you once again. Noticed errors in three places, where Month Clearance is "2 Insp. Cleared".

    Pl. find attached the excel highlighting the errors.

    Pl. do the needful.

    Thanks,
    Nagesh.
    Attached Files Attached Files

  63. #63
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combine multiple rows data in to one row

    As to column 161, The following has been placed in cell DT3 and copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    What should cell DN697 display, as there is no 1 Cleared for Shipment listed for Job HY-05950?
    Attached Files Attached Files

  64. #64
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Combine multiple rows data in to one row

    Hi JeteMc,

    Both "1 Cleared for Shipment" and "2 Insp. Cleared for Shipment" are to be considered as "Cleared phases" which will reflect in Col. DN. The formula you put is on that condition, and working for DN820 and DN821.

    Basically starting number in Col.M are divided in to two categories. 1 and 2 are considered as "Cleared" and between 3 to 6 are to be considered as "Yet to Clear".

    The formula and logic is working and sometimes it is showing error message.

    I Wrongly put Col.161 for Structured reference formula. I am unable to put for Col.162 as the formula range is not for entire column (from cell No.2 to the particular cell only).

    Pl. do the needful.

    Thanks,
    Nagesh.

  65. #65
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combine multiple rows data in to one row

    Formula for column DN:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Will have to look at column DU later.
    Attached Files Attached Files

  66. #66
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combine multiple rows data in to one row

    I believe that the following works, however I encourage you to insure that the results are as expected:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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