Hey guys, Quick question about Excel - would love some help from the spreadsheet wizards here. I’ve been asked to analyze some data, and as part of the process I need to count up some unique entries in this spreadsheet. Please let me know if you have ideas for an elegant solution. Here’s an example of the data that I’m working with: Hat Brown T-Shirt Green T-Shirt Purple Pants Blue Pants Charcoal Pants Black Pants Black The data I’m working with is similar to the table above, but is hundreds of rows and several columns. I’d like to accomplish two things: (1) Count up the number of discrete entries in column A – that number would be 3 (hat, t-shirt, pants) (2) Count up the number of different colors per article of clothing – that would be 1 for hat, 2 for t-shirts, and 3 for pants (the formula would ignore the second pair of black pants) Any suggestions? Thanks in advance.
Sort column A by alphabetical order. Insert column B. In cell B1 put in “1”. In cell B2 put in =if(A2 = A1, B1, B1+1). Fill down. The last number in that column will tell you how many unique entries you have in column A. That should solve problem (1).
Then you can build off of column B to solve your problem (2). All the 1’s will correspond to Hat or whatever, and 2’s to T-shirt. So put in the same if statement you did for the first problem again, but only fill it down for the 1’s first, then insert the same if statement for all the 2’s, etc.
PivotTable. Just select all of the data and go to insert -> pivottable. Keep clicking OK and it will create a new sheet with the pivottable linked to your data. You can choose how to summarize the data in the boxes on the right, and choose any hierarchy of data you want. You can probably figure it out on your own in about half an hour of clicking and dragging.
Here’s how I would do this: Column A has Hat Brown < – this is at row 2 T-Shirt Green T-Shirt Purple Pants Blue Pants Charcoal Pants Black Pants Black Starting at column B row 1 Hat T-shirt pants blue black purple Write this formula into B2 =IF(ISERR(SEARCH(B$1,$A2,1)),"",SEARCH(B$1,$A2,1)) drag down to row 8 and across to column G At row nine write this formula starting column B =COUNT(B2:B8) drag across to row G done. drink a beer.
Thanks for the thoughts, guys – looks like some good ideas. I think I’ll try them all and see which seems the most intuitive for multiple spreadsheets/data dumps. Pivot tables are probably what I’m most accustomed to. If I decide to use Pivot Tables, how can I count the number of unique entries in column A, assuming that that’s also going to be the first column of the two-column pivot table?
For Part A: =IF(LEN(A1:A7),SUMPRODUCT(1/COUNTIF(A1:A7,A1:A7))) For Part B, copy the A column somewhere, data remove duplicates then. Then beside each entry. Use this. =SUM(IF(FREQUENCY(IF(A$1:A$7=D1,IF(B$1:B$7<>"",MATCH(B$1:B$7,B$1:B$7,0))),ROW(B$1:B$7)-ROW(B$1)+1),1)) Beside each entry in the new without duplicate column. I am using D1 as the identifier. Remember both need shift+crtl+enter.
If I decide to use Pivot Tables, how can I > count the number of unique entries in column A, > assuming that that’s also going to be the first > column of the two-column pivot table? Well, if you add it to the row labels field it will only show the unique entries right? So then just enter a formula to count that, then copy the number and alt->e->s->v (paste values) in the a cell next to the formula. Then add the shirts or pants or whatever to the next level under row labels. Oh, just realized you are going to want to put a whole column of 1s next to your data. Then you can put the 1s column in the values box and use the sum function to get the count for each “clothing” type.
Thanks eureka. I was thinking about the whole column of 1’s as well. Not quite the most elegant solution, but it’ll do the trick…
numi Wrote: ------------------------------------------------------- > Thanks eureka. I was thinking about the whole > column of 1’s as well. Not quite the most elegant > solution, but it’ll do the trick… No prob, glad I could help and it was the best I could think of on short notice. Sometimes it’s not worth spending time working out an elegant solution if it’s not something you’re going to do at least weekly. Simple yet elegant, sophisticated yet demure, that’s how I like to build spreadsheets.
also can just do a advanced filter, copy to a new column & unique entries only, then run a count on the new column.