Solved

Excel - Choose Unique Rows (based on multiple columns)

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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