Hi,
Need to club multiple rows text data in to one row.
An excel file attached with necessary details.
Pl. help.
Thanks,
Nagesh.
Hi,
Need to club multiple rows text data in to one row.
An excel file attached with necessary details.
Pl. help.
Thanks,
Nagesh.
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.
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.
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.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
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.
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
Hi Alan,
Am using Office 2013 version. dont know how to update the version. Will try and change.
Thanks,
Nagesh.
Updated my profile.
Thanks,
Nagesh.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
Hi JetMc,
Noticed an error while working with the formula.
Attached sample excel file with necessary details.
Pl. help.
Thanks,
Nagesh.
Will be away from my computer for a couple of days but will look when I get back.
Ok.
Pl. do the needful once you are back.
Thanks,
Nagesh.
The formula in cells BP3 and down can be changed to read:Formula:Please Login or Register to view this content.
The formula in cells BT3 and down can be changed to read:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Hi JeteMc,
Need little correction in the formula.
Excel file attached with necessary details.
Pl. help.
Thanks,
Nagesh.
The following would be used instead of the current formula in column BN (which may eliminate the need for columns BO:BP):Formula:Please Login or Register to view this content.
Let us know if you have any questions.
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.
Paste the following into cell BN3 and copy down:Formula:Please Login or Register to view this content.
Paste the following into cell BP3 and copy down:Formula: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.
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.
I changed BN3 and down to read:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
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.
Try pasting the following into cell BN3 and double click the fill handle to copy down:Formula: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.
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.
Paste the following into cell BO2 and copy down:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
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.
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.
Hi JeteMc,
Pl. find attached the excel file by highlighting the requirement at Col.T, BR & BT.
Pl. do the needful.
Thanks,
Nagesh.
Please explain why BR86 and BR90 should be blank.
Since K6 and K9 doesnot beging with 3 to 6.
ex: BR85 is A1/2 because K85 begin with 6.
This seems to have solved the issue for column BR and BT.
Change the formula in BR3 and down so that it reads:Formula:Please Login or Register to view this content.
Also try the following in T3 and down:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
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.
This thread is marked as SOLVED!!!
Try changing the formula in BT2 and down so that it reads:Formula:Please Login or Register to view this content.
Similarly the formula in cells BP2 and down could be changed to read:Formula: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.
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.
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved' (again). I hope that you have a blessed day.
Hi JeteMc,
Noticed an issue with the formula you provided.
An excel file attached with necessary example.
Pl. help.
Thanks,
Nagesh.
Power QueryPlease Login or Register to view this content.
Please Login or Register to view this content.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
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.
Modify the formula in cells DU3 and down to read:Formula: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.
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.
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.
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 ??
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.
THank you. After all the effort put in by JeteMC it is only fair to use his formula: I'll put VBA on standby!!!
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.
Using structured references for the formula in the phases cleared column should restrict it to the table range:Formula: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?
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.
Changing the formula in the Phases Cleared column seems to work:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
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.
As to column 161, The following has been placed in cell DT3 and copied down:Formula: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?
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.
Formula for column DN:Formula:Please Login or Register to view this content.
Will have to look at column DU later.
I believe that the following works, however I encourage you to insure that the results are as expected:Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks