1

I am working with an Excel file that is pretty big, and will get bigger over time. The data is exported from a few different locations, so in the end I have this file that lists a single same item in multiple rows.

Almost all of the information is the same, except for my last few columns which lists different features of that particular item. The issue is that when an item has the feature called out for that column, a "YES" appears in that cell. And instead of an item with multiple features just having a "YES" listed for each column that applies, it creates a separate row for it, and leaves the other feature columns blank.

So my file lists 6 different features. If a product supports 4 of those 6 features, it's given 4 rows, each saying YES to only one feature, as opposed to just one row for each product and listing YES where appropriate.

The data may change over time so I am hoping there's some kind of way to summarize the data as needed after new exports to end up just with 1 row for each unique item, with all of its details.

Here's an example:

Product ID | SKU | Feature1 | Feature2 | Feature3
product1   | 123 | YES      | BLANK    | BLANK
product1   | 123 | BLANK    | BLANK    | YES
product2   | 127 | BLANK    | YES      | BLANK
product2   | 127 | BLANK    | BLANK    | YES
product3   | 383 | YES      | BLANK    | BLANK
product3   | 383 | BLANK    | YES      | BLANK
product3   | 383 | BLANK    | BLANK    | YES

I'd like to end up with:

Product ID | SKU | Feature1 | Feature2 | Feature3
product1   | 123 | YES      | BLANK    | YES
product2   | 127 | BLANK    | YES      | YES
product3   | 383 | YES      | YES      | YES
Scott - Слава Україні
  • 22,532
Nate
  • 13

1 Answers1

1

Instead of "YES" or "BLANK", I used 1 or an empty cell. Then dumped it into a pivot table, which just involves dragging and dropping the fields and selecting a few options, like leaving off subtotals.

screenshot

I didn't do anything fancy, and didn't make it pretty. Either count or sum can be used to aggregate; it defaulted to count (and I left the default column labels).

I did a quick test with YES instead of 1, and count works with YES as well, and that doesn't even involve any data cleanup. So this method gives you the desired consolidation with literally a couple of minutes work, and no VBA required.

fixer1234
  • 28,064