combine identical cells and get count

**cclough27** · 06-10-2008, 10:42 AM

I'm a newbie here so please forgive me if I explain this wrong!

I don't know if I am explaining this correctly. I have a list of transactions on accounts that have the account name multiple times. I want a count of just those account names but without the copies for the different transactions. I have like 5 entries (transactions) on say Bob Smith's Towing I need it to combine all five entries into one or delete all but one so I have a true count of how many accounts I have. Does this make sense?

Thanks!!

**NBVC** · 06-10-2008, 10:50 AM

So you only want to count unique account names?

Try:

=SUMPRODUCT((A1:A1000<>"")/COUNTIF(A1:A1000,A1:A1000&""))

adjust the ranges to adapt to your list.

**cclough27** · 06-10-2008, 10:57 AM

no basically i have an account name multiple times and need a count of just the first time its listed...not the total of all of it?

clear as mud??

**NBVC** · 06-10-2008, 11:04 AM

That should be what the formula does...

If you have 5 "Joe's Towing" and 10 "Jack's Towing" in the list.. then formula will return a count of 2.

Is that not what you need?

If not... please attach a sample of your data and what you need returned as a result.

**cclough27** · 06-10-2008, 11:18 AM

awesome thanks!!

is there a way to combine those multiple lines so then I can use that for a pivot table?

**NBVC** · 06-10-2008, 11:22 AM

That's what a pivot table does... it consolidates the list and gives summaries (either counts of records, sums of records, averages, etc).

Here are some Pivot Table tutorials....

http://www.microsoft.com/dynamics/us...s_collins.mspx

http://www.cpearson.com/excel/pivots.htm

http://peltiertech.com/Excel/Pivots/pivotstart.htm

**cclough27** · 06-10-2008, 11:31 AM

i know but i need to use that count i just found (first line only) and then use that in my pivot table.

**NBVC** · 06-10-2008, 11:35 AM

I'm confused....

you may have to post a sample sheet detailing your haves and want-to-haves.

**cclough27** · 06-10-2008, 11:56 AM

i can't attach it for some reason..says its too large !!

**NBVC** · 06-10-2008, 12:05 PM

Did you try to zip it first...

if still too large... slim the file down first with some relevant data and then send it.

**cclough27** · 06-10-2008, 12:25 PM

here it is. i have added the pivot table i am needing to do but the pol_idx is what i need just the first line count for - right now it is counting everytime it sees that pol_idx.

tia!!

**NBVC** · 06-10-2008, 01:07 PM

I am sorry, but I still don't fully get your requirement...

I have reattached your workbook with a calculation using the above formula. It counted 661 unique records.

I also added a simple Pivot Table that counts how many times each unique item occurs...

If none of these are what your are looking for, then please show your expected results using the data you have in this workbook.

**cclough27** · 06-10-2008, 01:22 PM

I only want the count of the pol_idx to be the first line - not the number of times it is in the report which it is doing now with your pivot table. i'm having to give a report of how many accounts we have in each state by the type and if i just input the count of the pol_idx it gives me a false number because it is including the mulitples (i.e. the pol_idx number is repeated). i need to somehow have it only count that first line (first time its (pol_idx) is listed) in the pivot table. is this possible?

hopefully this makes more sense. i'm sorry!

i really appreciate your help!!

thanks so much!

**NBVC** · 06-10-2008, 01:27 PM

What about using Data|Filter|Advanced Filter to extract a list of unique records...

see column N in the attached...

**cclough27** · 06-10-2008, 01:31 PM

but can i use that in a pivot table? like i mentioned before i need to show by state and type with the unique items...

it may not be possible.

**NBVC** · 06-10-2008, 01:39 PM

I doubt you can do that with Pivot Tables (if I am understanding you correctly).. Pivot tables summarize they don't extract certain records.

You can possible extract the unique list from column A as I did with Advanced Filter and then use Vlookup to extract the detail from the first occurance of each item...

See again the sample sheet Columns N:S

**cclough27** · 06-10-2008, 02:50 PM

this might work!!

just tell me how to do it then?

i'm familiar with Vlookup but not the way you did it. also i an unfamiliar with the advanced filter...

see all the help i need??

thanks again!

**NBVC** · 06-10-2008, 02:58 PM

Go to Data|Filter|Advanced Filter

Select "Copy to another location"

in the List Range enter (or select) the range to filter (Column A only).

Leave Criteria range blank

In the Copy to enter the top most cell reference where you want the data to go... it must be on the same sheet though. (You can copy/paste it later if you need to put in another sheet).

Then apply the Vlookup formula... I used Column(B$2) as the 3rd (column Index) argument.. so that I can copy it across and it steps up to the next column number automatically.

**cclough27** · 06-10-2008, 03:12 PM

okay i get the filter part - i'm confused on the VLookup now!! I'm still very new to Vlookup so I have only used it once or twice a couple different ways.

THanks!!

**NBVC** · 06-10-2008, 03:15 PM

Originally Posted by cclough27

okay i get the filter part - i'm confused on the VLookup now!! I'm still very new to Vlookup so I have only used it once or twice a couple different ways.

THanks!!

Have a look at Vlookup in Excel help files.. it explains it well and gives examples... there are probably thousands of examples within this forum too!

**cclough27** · 06-10-2008, 03:39 PM

do you know if i would be able to sum the income and premium i have for each pol_idx i have

**NBVC** · 06-10-2008, 03:42 PM

Take a look at Sumif in Excel Help... that is the function you want...

**GrahamBlackwell** · 06-11-2008, 04:01 AM

Have added a column of data so I can use Min in the pivottable to count occurence of Pol_idx in each state

Hope this is what you were after.

combine identical cells and get count

LinkBack

Thread Tools

Rate This Thread

Display

combine identical cells and get count

Is this what you mean?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions