find unique values in different spreadsheets.

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: find unique values in different spreadsheets.

  1. #1
    Board Regular
    Join Date
    Oct 2008
    Location
    Bedfordshire (UK)
    Posts
    1,073
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default find unique values in different spreadsheets.

     
    Hi

    I have 2 years worth of spreadsheets and I need to make a list of all the unique items by item number (UPC) from them all,

    The spreadsheets are all in two directories 2007 and 2008 the named Week 1 to Week 52 (2007) & Week 1 to Week 48 (2008)

    All the items unique code (UPC) are in column C but could be from C4:C300 to C4:C350 in each spreadsheet.

    How can I do this or will i need to copy and past col C from every spreadsheet into a master sheet to do this.....

  2. #2
    Board Regular
    Join Date
    Oct 2008
    Location
    Bedfordshire (UK)
    Posts
    1,073
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: find unique values in different spreadsheets.

    I'm guessing what I was trying to do was not possible..

    I have now copied 60 columns of data from 60 different spreadsheets into a single spreadsheet.

    This spreadsheet is now from A4:BF30 and only contains item numbers
    75% of these are duplicates and I need to find create a list of uniqe item numbers from this massive list.....

    I was going to go down the pivot table route but I think that will not work due to the high number of columns.....

    I'm tired and want to go to bed, It was the Christmas party on Saturday and my brain is still not functioning yet

  3. #3

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: find unique values in different spreadsheets.

      
    Hi,

    Create backup copies of your file. Not tested.

    Code:
    Sub kTest()
    Dim FileFolder As String
    Dim dic As Object, w(), v, n As Long, a, Flg As Boolean
    Dim fName As String, wb As Workbook, aWB As Workbook, ws As Worksheet
    
    Set dic = CreateObject("scripting.dictionary")
    dic.comparemode = vbTextCompare
    FileFolder = "C:\2007\" 'change to suit
    fName = Dir(FileFolder & "Week*.xls")
    ReDim w(1 To Rows.Count, 1 To 1)
    With Application
        .ScreenUpdating = 0
        .EnableEvents = 0
        .DisplayAlerts = 0
    End With
    Set aWB = ActiveWorkbook
    Flg = False
    DoAgain:
    Do While fName <> ""
        If fName <> aWB.Name Then
            Set wb = Workbooks.Open(Filename:=FileFolder & fName, UpdateLinks:=0)
            With wb.Sheets(1)
                a = .Range("c4", .Range("c" & Rows.Count).End(xlUp))
            End With
            For Each v In a
                If Not IsEmpty(v) Then
                    If Not dic.exists(v) Then
                    n = n + 1: w(n, 1) = v: dic.Add v, Nothing
                End If
            Next
            Erase a
            wb.Close False
            Set wb = Nothing
            fName = Dir()
        End If
    Loop
    If Flg = False Then
        FileFolder = "C:\2008\" 'change to suit
        fName = Dir(FileFolder & "Week*.xls")
        Flg = True
        GoTo DoAgain
    End If
    Set ws = aWB.Sheets.Add
    ws.Name = "UniqueCodes"
    With ws.Range("a1")
        .Value = "Unique Codes"
        .Offset(1).Resize(n).Value = w
    End With
    Set dic = Nothing
    With Application
        .ScreenUpdating = 1
        .EnableEvents = 1
        .DisplayAlerts = 1
    End With
    End Sub
    Last edited by Krishnakumar; Dec 8th, 2008 at 12:47 PM.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com