?
Solved

Excel - Choose Unique Rows (based on multiple columns)

Posted on 2012-12-28
4
Medium Priority
?
204 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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

649 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