Comparing & Counting Data

[Walt]

I have created an Excel workbook containing several tabs. The workbook is to
keep track of a project for a social organization, Flags for Patriotism
Program. Briefly, the Flags Program posts American Flags at participating
businesses on specific holidays throughout the year. The entire project is
divided into 6 routes (A-F).

As such, I have a tab for each route. Other than the business names and
addresses, the tabs are identical to start. The user enters the name(s) of
the people that post a route into cells A4, K4, A5, and K5 (room for 4
names). Often times, one person will post more than one route.

On a "Summary" tab, I need to count the people that were involved in the
process. However, if I posted 2 of the routes, it counts me as 2 people and
lists my name twice. How can I get it to count me and list my name only one
time in a certain field on the "Summary" tab, say cell BW6?

 

[Shane Devenshire]

Hi,

It would help if you showed us a sample of the data. If all you want is a
list of the unique names in a column you can look at Advanced Filter, Unique
Records.

 

[Walt]

OK... Here is a sample of some data...

Tab A, cell A4 = W.A., cell K4 = C.C.
cell A5 = Blank, cell K5 = Blank

Tab B, cell A4 = W.A., cell K4 = C.C.
cell A5 = Blank, cell K5 = Blank

Tab C, cell A4 = R.H., cell K4 = M.C.
cell A5 = Blank, cell K5 = Blank

Tab D, cell A4 = R.H., cell K4 = M.C.
cell A5 = Blank, cell K5 = Blank

Tab E, cell A4 = L.C., cell K4 = E.O
cell A5 = Blank, cell K5 = Blank

Tab F, cell A4 = L.C., cell K4 = E.O
cell A5 = Blank, cell K5 = Blank

I want to show how many people helped with the posting. Because there were 2
people posting each of the 6 routes, it comes up as 12 names. Through a
series of 'if/then' statements, I can get the numerical number (6) correct,
but I cannot figure out how to get it to list the names "W.A., C.C., R.H.,
M.C., L.C., and E.O" to come up only one time, for a total of six names.
Everything I try to do comes up with 12 names - each name listed twice. I
would like the names to appear in:

Tab S, cells BA5:BF9, then be able to determine which names are repeats
(therefore, counting the repeated name(s) only one time. Instead of showing
12 names, it should show 6 names.

I hope I have explained things better. Thank you for the earlier post to
attempt to help. The advanced filter idea almost worked. For some reason, it
counted the first two people twice, but everyone else once.

 

[Walt]

I'll try to explain things a little better, but it looks as if the filter
idea may work. It's something I haven't tried yet, anyway.

The Flags for Patriotism Program is a project for a social organization in
which members of the organization post American Flags at participating
businesses on specific holidays of the year, and a few extras now and then,
for $50 per year.

The project is divided into 6 different routes around town, posting a total
of 120 Flags around town. The tabs for the six different routes are labeled A
thru F. There is a "Summary" tab at the end that will print out the pertinent
information needed for the paperwork for the project.

The tabs for the routes are identical with the exception of the business
names and addresses. The names of the people that posted the Flags for a
particular route are in A4, K4, A5, and K5. [We've never had 24 people help
at one time before, but there's room for it should it ever happen.] Each
vehicle that is involved with the project is given a vehicle number (1-6).
[We've also never had 6 cars before, but there is room for it should it ever
happen.]

The information would look like this:

Tab A - Cell A4 = W.A. - Cell K4 = C.C.
Tab A - Cell A4 = Blank - Cell K5 = Blank

Tab B - Cell A4 = W.A. - Cell K4 = C.C.
Tab B - Cell A4 = Blank - Cell K5 = Blank

Tab C - Cell A4 = R.H. - Cell K4 = M.C.
Tab C - Cell A4 = Blank - Cell K5 = Blank

Tab D - Cell A4 = R.H. - Cell K4 = M.C.
Tab D - Cell A4 = Blank - Cell K5 = Blank

Tab E - Cell A4 = L.C. - Cell K4 = T.F.
Tab E - Cell A4 = Blank - Cell K5 = Blank

Tab F - Cell A4 = L.C. - Cell K4 = T.F.
Tab F - Cell A4 = Blank - Cell K5 = Blank

In this example, there are 3 vehicles, each with 2 people, totaling 6
people. Using numbers and comparing NUMBER of people in each car with a
vehicle number, I can make the numerical version work exactly how I need it
to through a series of 'count' and 'if/then' statements comparing vehicle
numbers, etc. However, using the same method does not work with the TEXT of a
person's name. Everthing I've tried lists each name twice (because they each
did two routes), for a total of 12 names listed.

On the "Summary" tab, the information [people's names] would be gathered in
cells A44:F48.

Any help you would be able to give is appreciated. Thank you, in advance.