Does anyone know of an Excel formula that would count the unique values of the first 11 digits (all digits before the underscore)
IMAGE_ID
08676724002_1038260.eps
08676724006_1130460.eps
08676724006_1453134.eps
08676750004_1405277.eps
08676750006_1101494.eps
08676750006_1453135.eps
08676751006_1353510.eps
08676751302_1096034.eps
08676751302_1117999.eps
08676751302_1318708.eps
08676751302_1336658.eps
08676751302_1353509.eps
08676751306_1094061.eps
08676752004_1405278.eps
08676752006_1082618.eps
08676752006_1453136.eps
and summarize them as such:
IMAGE_ID COUNT
08676724002_1038260.eps 1
08676724006_1130460.eps 2
08676750004_1405277.eps 1
08676750006_1101494.eps 2
08676751006_1353510.eps 1
08676751302_1096034.eps 5
08676751306_1094061.eps 1
08676752004_1405278.eps 1
08676752006_1082618.eps 2
You can do this in a few steps...
1. in B4, enter formula: =LEFT(A4,11) copied down
2. Enter a title in B3
3. Select B3:B19 and go to Data|Advanced Filter.
4. Select Copy to Another location and enter a cell, say, F3 and select Unique Records Only,
5. Enter formula in G4: =VLOOKUP(F4&"*",$A$4:$A$19,1,0) copied down
6. Enter formula in H4: =COUNTIF($A$4:$A$19,F4&"*") copied down.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks