Log In
Register
Home
April 23, 2015
Offline
Hi folks,
Hope everyone is well. Another one I am looking for help with...
In the attached workbook you will see I have geographical locations and stations listed by date. I am trying to figure out a way to identify can count duplicate instances, a duplicate would be defined as a Location, (column C), a Client, (Column D), and a station reference, (columns I-R), that occurred on different dates.
For example, if Location "Davis" and Client "Newfield" and station reference "402" occurred on different dates it is a duplicate and I want to be able to count it.
I actually hacked my way through it by concatenating columns, C, D, and I-R independently, then sorted the resulting list to find the duplicates and count them in a pivot table. That worked but is very long-winded and not very elegant.
Does anyone have a more efficient answer?
Alan
July 16, 2010
Offline
Hi Alan,
Thanks for sharing your sample data. You can do this with a PivotTable without the need to concatenate columns. See example attached.
Mynda
April 23, 2015
Offline
Hi Mynda,
Many thanks as always for the help, that solution certainly works for duplicate rows. My initial posting was not very clear and I realize now the "duplicate" description I used was not correct. What I am really trying to isolate and consolidate are repeated station visits on different dates.
In the example attached you will see I have highlighted these repeat entries, orange fill for station 101 and blue fill for station 102. So it doesn't matter if the station reference is listed under station1 or station2 or station3 etc. If it is the same station reference for the same client and location on a different date it is a repeat occurrence. In the attached example the count for station 102 is four.
Hope that makes more sense. Is there a better way to generate these counts than the concatenation approach I originally took?
cheers,
Alan
July 16, 2010
Offline
Hi Alan,
Thanks for clarifying and providing a clearer example. You're having to jump through hoops because your data is not in a tabular format. First, fix the layout and unpivot the 'Station #" columns. Then you can easily find the answers you need with a PivotTable. See attached.
Mynda
April 23, 2015
Offline
Thanks Mynda,
It had crossed my mind that the format was the limiting factor but was looking for an "easy" way to avoid having to go through and restructure it, (lazy I know!). Your query looks like it has done the trick - many thanks for the help.
Alan
1 Guest(s)
