Making a list of discrete numbers (names?) and occurences of each

[partsman_ba]

Here's what I have:

A spreadsheet sent to me with a list of filter part numbers, with each
vehicle having it's own row and each type of filter (air, oil, etc) having
it's own column. I would like to create from this data a sorted list of each
different filter part number, along with a count of how many times that part
number is on the sheet. Part numbers contain both numeric and alpha
characters in some cases. It seems like something not so hard to do, but I've
been bashing my head for some time!

 

[partsman_ba]

Ideally, I would like it to automatically update - if I put in a new filter
number, it would be added to the sorted list with an occurence of 1, or if I
add an already used filter to a new vehicle, it will add another occurence to
the list. Am I asking too much?

 

[Roger Govier]

Hi

Provided each of your columns has a header title in row 1, it sounds an
ideal candidate for a Pivot Table report.
Take a look at Debra Dalgleish's site and scroll down to Pivot Tables
for assistance on how to set one up.
http://www.contextures.com/tiptech.html

or Mike Alexander's site
http://www.datapigtechnologies.com/flashfiles/pivot1.html

 

[Max]

One alternative using non-array formulas which automates the extraction of
the uniques list from 4 source cols and the corresponding counts of the
uniques ..

Illustration in this sample construct:
http://www.savefile.com/files/841298
Multicol merge n uniques extract n count.xls

Source data assumed in cols A to F, data from row5 down
where col C = Lube data, col D = air-primary data,
col E = air-secondary data, col F = fuel-inner data

Using 4 adjacent cols to the right, cols G to J

In G5:
=OFFSET(C$5,INT((ROWS($1:1)-1)/4),MOD(ROWS($1:1)-1,4))
Copy down to cover the max expected extent of aggregated data in the 4
source cols C to F. If you expect source data per col to span up to say 100
rows down, then copy down by a total of 400 rows (4 cols x 100 rows = 400
rows). Col G functions to merge the contents of cols C to F into a single
col. Empty source cells will be returned as zeros.

In H5:
=IF(G5=0,"",IF(COUNTIF(G$5:G5,G5)>1,"",ROW()))
Leave H1:H4 blank. This is a criteria col to ignore the zeros and flag the
unique items extracted in col G.

In I5:
=IF(ROWS($1:1)>COUNT(H:H),"",INDEX(G:G,SMALL(H:H,ROWS($1:1))))

In J5:
=IF(I5="","",COUNTIF(G:G,I5))
Select H5:J5, fill down to the same extent as for col G. Hide away cols G &
H. Col I will return the list of unique items from cols C to F, while col J
returns the counts of these unique items. All results will be neatly bunched
at the top.

You could then just do a simple copy n paste special as values for cols I &
J elsewhere, and use Data > Sort menu to sort by the uniques col.

 

[partsman_ba]

Thanks Max - exactly what I was looking for. Looks like the BASIC programming
I used to do on my TRS-80 Model I back in 1981!

 

[Max]

welcome, good to hear it worked for you.

 

[susaneboa]

Hi,
Would this work if I wanted to convert a list of sales orders from the
customer id listed in column A with other details to the right in columns B-Z
including the order date

to

a 3-5 column report consisting of:
column A - customer id column B - 1st order date column C - 2nd order
date and so on

that way I could see the customer with all of the order dates across the sheet

Thanks for any help!! I'm wracking my brains trying to figure it out.,