+ Reply to Thread
Results 1 to 2 of 2

Using 'Fuzzy Logic' in Pivot Results?

  1. 04-24-2008, 07:47 PM #1
    rokuk
    rokuk is offline
    Registered User
    Join Date
    10-22-2007
    Posts
    37

    Using 'Fuzzy Logic' in Pivot Results?

    Hi there.

    I am creating pivot tables and charts for a large spreadsheet of data (thousands of rows). I have a pivot table set up as I want it (and charts based on it), however there are so many row entries that reference the same products, but in slightly different ways. Think:

    "Windows2000" and "Windows-2000" and "Windows 2k" - which all refer to the same operating system, but the user has input them differently. Take this and magnify it by 50 and that's about how many of these "nearly the same" entries I have.

    How could I make it so only ONE row (for example: "Windows 2000") is reported in the pivot table, and all "similar" rows are counted in the sum for this ONE row ("Windows 2000"), instead of listing every row separately? The only thought I have now is -maybe- writing something in VBA to try to somehow transform this column by taking out anything like a "-" or extra spaces where they don't belong... but even this I'm not entirely sure how to implement.

    Any suggestions would be EXCEPTIONALLY appreciated.

    Thanks in advance
    Reply With QuoteRegister To Reply
  2. 04-24-2008, 08:07 PM #2
    Portuga
    Portuga is offline
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852
    You need to really clean up your data. One magic macro will not appear to solve those issues.

    You could use diferent aproaches, one I use quite often is the auto filters. Its quite "hands on" but it works.

    Use the autofilter to select for instance, "Contains" "windows" and second criteria "contains" "2". Choose the unique name you want and just drag it down on those cells.

    After a few other criteria, your data should be cleaner and you can use the pivot table to spot "duplicates".

    In your original data, you could then use validation and dropdown lists to narrow the products you use, solving the problem of different spelling.
    If you found the solution to your question. Mark the thread as "Solved"
    Thank everyone that helped you with a valid solution by clicking on their

    There is no such thing as a problem, only a temporary lack of a solution
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1