Solved

Excel - Choose Unique Rows (based on multiple columns)

Posted on 2012-12-28
4
197 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
  • 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

813 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now