+ Reply to Thread
Results 1 to 4 of 4

Find and Replace on some duplicates in a huge data file

  1. #1
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Find and Replace on some duplicates in a huge data file

    All,

    I have a file with 300k lines of SKU data. This is data by month for 12 months (so roughly 25K SKUs per month, with changes in SKUs each month.) I take this data and pivot it and drop it into a file.

    I noticed last month when I prepared that the SKUs have names next to them, and sometimes the information is pull by the name instead of the SKU. Unfortunately, in the master data, someone entered different names in a few of the months. So for example, in some months SKU 0012 is Green B and in other months it's Green Beans. I need the names to be the same.

    Is there a quick way to fix this given that I'll have 12 records and maybe 3 will be one name and 9 will be there other? Finidng and replacing every one is a chore because on pivot there are 40k. I don't think I can do a quick remove duplicates because it would remove multiple months. I just want each SKU to have the same name.

    Thanks!

  2. #2
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Re: Find and Replace on some duplicates in a huge data file

    What I've done so far is create a new pivot that has two columns. One is the SKU and one is the SKU names. I did a remove duplicates on that. Now I can go back to the original data and add another column and do a lookup on SKU against the name, then paste the new values in on top of the new ones.

    This works, but for my own edification, is this the best way to do this?

  3. #3
    Registered User
    Join Date
    03-06-2014
    Location
    Cape Cod, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Find and Replace on some duplicates in a huge data file

    Your approach is the best one. Have a separate worksheet that is a definition table of your SKUs. With SKU number and SKU name. Then in your worksheet with 300k lines, do a lookup of the SKU number and retrieve the name. Then use the SKU number and SKU name (from lookup) as part of the pivot table output.

  4. #4
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Re: Find and Replace on some duplicates in a huge data file

    Quote Originally Posted by gotchuck View Post
    Your approach is the best one. Have a separate worksheet that is a definition table of your SKUs. With SKU number and SKU name. Then in your worksheet with 300k lines, do a lookup of the SKU number and retrieve the name. Then use the SKU number and SKU name (from lookup) as part of the pivot table output.
    Thanks very much!

+ 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. I need help. Huge data source, I need to clean it up. Find and move data macro.
    By sangreal2007 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-20-2012, 02:26 PM
  2. Replies: 2
    Last Post: 07-22-2010, 02:57 PM
  3. Replies: 1
    Last Post: 07-13-2010, 10:09 AM
  4. Generate GUID, Find Duplicates, Replace, Rescan
    By talkinggoat in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-01-2010, 03:29 AM
  5. [SOLVED] huge huge excel file... why?
    By Josh in forum Excel General
    Replies: 12
    Last Post: 02-09-2006, 06:00 PM

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