Solved

Excel - Choose Unique Rows (based on multiple columns)

Posted on 2012-12-28
4
200 Views
Last Modified: 2012-12-30
Hi everyone!

I've got an Excel problem...in one worksheet, I have a listing of all possible products (each with a model and a width -- these are for an inventory of shoes).  One column is for the model and the next column is for width (there is also a column for the SKU, but this is irrelevant).

I have a second worksheet with a final tabulation of the inventory of each size, etc., but would like each combination (model with width) to be on the second worksheet once.

Example...

SKU                          STYLE        SIZE   WIDTH
873370009283      XPX761      11.0         W
873370009177      XPX761      11.0         M
873370009290      XPX761      11.5         W
873370009306      XPX761      12.0         W
873370009191      XPX761      12.0         M
873370009313      XPX761      13.0         W

The second worksheet should have... (because out of this list, these are the only two unique rows when only considering style and width).

STYLE      WIDTH
XPX761   W
XPX761   M

Any ideas on how this can be done using a formula (for when there are additional rows added to the 1st worksheet)??

Thanks!!!!
0
Comment
Question by:tru504187211
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 13

Accepted Solution

by:
Shanan212 earned 500 total points
ID: 38727169
Best bet is pivot table.

Make a pivot table on sheet 2. Choose source as sheet 1 (which will be adjusted). Make sure the source extends below the current data limit to accomodate future data additions.

Then every time the data is added, simply refresh the pivot on sheet 2. It will always pick up unique values.

Post a sample set if you want to see example
0
 

Author Comment

by:tru504187211
ID: 38727206
OK...

* SKU Info Worksheet would have...

873370009177      XPX761      11.0      M
873370009290      XPX761      11.5      W
873370009306      XPX761      12.0      W
873370009191      XPX761      12.0      M
873370009313      XPX761      13.0      W

* Working Data Worksheet would have...

A list of SKU's taken from the scanning gun...

* Report Worksheet would have...

Style       Width      4.0      4.5      5.0      5.5      6.0      6.5      7.0      7.5      8.0      8.5      9.0      9.5      10.0      10.5      11.0      11.5      12.0      13.0      14.0      15.0      16.0
...is the header.

For each size, their would be a count based on the SKU Info worksheet (what SKU is what size).
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 38727430
Is it possible for you to provide a worksheet with the above sample data? Thanks!
0
 
LVL 10

Expert Comment

by:SANTABABY
ID: 38728517
Attached is a sample workbook with formula, if you have questions, please ask.
Book2.xlsx
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question