Hi,
I have to combine multiple line items to make one representative single code but for the life of me have not been able to find a way to do it effectively. I have attached a sample file. Please do go through it and let me know if you know of a solution Thanks in advance.
Input:
Multiple Lot #, Code
Lot1, ABC123
Lot1, ABC124
Lot1, ABC145
Lot1, ABC147
Output:
Unique lot number, Single short code
Lot1 - ABC123,4,45,7
Last edited by vgr; 07-19-2014 at 01:33 AM.
Try using a helper column - I used D, you can use what you want, and hide it if needed.
In D2, copied down...
=IF(A2=A1,D1&","&C2,C2)
Then in J2, copied down...
=OFFSET($A$1,MATCH(I2,$A$2:$A$21,0)-1+COUNTIF($A$2:$A$21,I2),3)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thank you Ford,
However, the major part of the problem is that I have to manually find out the characters to be used to make the final code. What I basically have is a Lot # and code... I need to pull out the single code from this information for multiple lots! Would be great if you could possibly figure something out on those lines. Thanks again.
Last edited by vgr; 07-19-2014 at 01:02 AM.
First with the built in text to column.
After that I run this code.
See the attached file.
Please Login or Register to view this content.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Thank you for responding Oeldere. There is just one problem though, you took the output to be the input! The goal of this exercise was to reduce those 20 lines present in the sheet to 3. Only column A and B in sheet 1 are given. Column I & J is what one would expect in the output.
Thank you again and I hope you are able to figure out a way to do this.
with formula and a filter.
See the attached file.
Here is another solution for your consideration:
I also used a helper column D and the same formula as FDibbins.
The formula that I used in J2 is:
Formula:
Please Login or Register to view this content.
Last edited by newdoverman; 07-19-2014 at 10:59 AM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
OK this is a bit messy and uses a bunch of helpers.
In C2, copied down...
=IF(E2<>E1,E2&F2&G2&H2&I2&J2&K2,IF(F2<>F1,F2&G2&H2&I2&J2&K2,IF(G2<>G1,G2&H2&I2&J2&K2,IF(H2<>H1,H2&I2&J2&K2,IF(I2<>I1,I2&J2&K2,IF(J2<>J1,J2&K2,IF(K2<>K1,K2,"")))))))
Then move your summary to start in column O and copy this into E2:K2 and copy down
=MID($B2,COLUMN(A$1),1)
You can hide all of these columns
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks