Excel move duplicates macro

I need a macro that will find the duplicate values of the first word in columns F and G and sort column G with it's duplicate in column F. Please see attached of shorten version of file. The entire file consists of 2303 lines A thru F and 2516 lines in column G. Thanks!
excel-sample.xlsx
LVL 9
jmac44Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
I'm not sure what you mean. Do you just want to sort each column on the first word (part number?) in each column?
0
jmac44Author Commented:
Forget I said sort bad choice of words. Column g's first word has to match columns f's first word. There is a number on the end of column g delimited by a # that I need to do a text to column after I get them matched up. That is the key to this I need that final number in the report.
0
Martin LissOlder than dirtCommented:
Could you attach a workbook that has say 20 or 30 records with "before" and "after" sheets, with the "after" sheet showing how you want the data to look, including the text to column?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

jmac44Author Commented:
I've already attached one. It just doesn't have an after example. I'm sorry your have trouble understanding what I need. I don't have time to construct the after at this moment but perhaps I'll will later.
0
Martin LissOlder than dirtCommented:
OK, I'll wait until you post the "after" sheet. Remember I only need 20 or 30 rows.
0
jmac44Author Commented:
Before and After file uploaded Per your request Martin.
Before-and-After-sample.xls
0
Martin LissOlder than dirtCommented:
I had some time. See if this does what you want.

Sub SplitPartNumber()

    Columns("G:G").Select
    Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="#", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("F:F").Copy
    Columns("G:G").Select
    ActiveSheet.Paste

End Sub

Open in new window

0
jmac44Author Commented:
Sorry I have not explained this very well, but all that does is text to column CUST_PN and then copy column F to Column G.

Column F has a product description. Column G has a product description and cust_pn but column G is in a different order than column F. I need to put column G in the same order as column F then text to column cust_pn out.

All I'm looking for is a macro that puts column G in the same order as column F so the product descriptions match on the first word with out changing anything in columns a thru f.

The text to column or anything else I can do myself.
0
Martin LissOlder than dirtCommented:
You are correct, that's what I did, however (and I may have missed something) the output when I ran that macro looks exactly like your "after" sheet.
0
Martin LissOlder than dirtCommented:
So if there are differences please tell me the first row they occur in.
0
jmac44Author Commented:
Ok I'm going to take one more try at explaining to you what I need and if you still don't understand then I'm giving up. Columns A thru F are from a report that I need. The information in it are correct with the exception of it is missing CUST_PN. I wrote another report that has Product description and Cust_pn combined and copied and paste it to the original report. The problem is that the copied and pasted "column G" is not in the correct order as the orginal because there are more rows than the original. I need to find a way to line up the part description so that they match because the customer Cust_PN has to match the part description column F and Column G.
0
Ejgil HedegaardCommented:
Something is wrong with the After sheet combining Product and PN
First Product (column F) is 25C150BCG0H 1/4-20 X 1 1/2 CARRIAGE with CUST_PN = SCB1434 in Column H.
The value in column G (Before) that match is 25C150BCG0H 1/4-20 X 1 1/2 CARRIAGE#GCB14112
Here CUST_PN is GCB14112.

It looks that what you want is a simple lookup.
Copy column G to J on sheet Before.
Use "Text to Columns" to split, using # as delimiter.
Make a Vlookup in column H, to the 2 split columns J:L, using Product in column F as search value.
Convert the result (H) to values = Copy and paste as values.
Delete what is not needed, column G, J and L.
See file.
Before-and-After-sample-1.xlsx
0
Glenn RayExcel VBA DeveloperCommented:
I think what was confusing was that column G data was from a separate list but adjacent to the report data in A:F.  If one were to move column G to another sheet, split it into its two parts, then do a lookup on the part number, it would have been easier to resolve.  

I've attached a workbook that you can re-populate with your actual data; just copy down the formulas in the two sheets to do the split and lookup.  You just paste your report data in A:F on the "Main" sheet, then paste your original column G data in column A on the "Lookup" sheet and then column G will return the Cust_PN if found.

Regards,
-Glenn
EE-excel-sample.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmac44Author Commented:
Thanks, I'm awarding Glen the higher points because I can actually compare side by side and prove that Cust_PN is in the proper location.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.