Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Making a list of discrete numbers (names?) and occurences of each
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! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Making a list of discrete numbers (names?) and occurences of each
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? "partsman_ba" wrote: 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! |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Making a list of discrete numbers (names?) and occurences of each
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/f...es/pivot1.html -- Regards Roger Govier "partsman_ba" wrote in message ... 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? "partsman_ba" wrote: 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! |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Making a list of discrete numbers (names?) and occurences of e
Roger,
Here's a small piece of what the top looks like: 4J FILTERS UNIT # YR/MAKE LUBE AIR FUEL PRIMARY SECONDARY INNER 10 07 BLUEBIRD 7182 6861 3231 or 3411 11 07 BLUEBIRD 7182 6861 3231 or 3411 12 07 BLUEBIRD 7182 6861 3231 or 3411 13 07 BLUEBIRD 7182 6861 3231 or 3411 14 08 BLUEBIRD 7182 6861 3231 or 3411 15 08 BLUEBIRD 7182 6861 3231 or 3411 20 03 BLUEBIRD 7182 6500 3231 or 3411 21 06 BLUEBIRD 1791XE 6861 6862 3231 or 3411 So, not all fields are full, and headings are split - can it still work? "Roger Govier" wrote: 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/f...es/pivot1.html -- Regards Roger Govier "partsman_ba" wrote in message ... 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? "partsman_ba" wrote: 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! |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Making a list of discrete numbers (names?) and occurences of e
What I would like on, say, Sheet2 would be:
1791XE 1 3231 or 3411 8 6500 1 6861 7 6862 1 7182 7 and if I add a new number to Sheet1, it shows up on my list "partsman_ba" wrote: Roger, Here's a small piece of what the top looks like: 4J FILTERS UNIT # YR/MAKE LUBE AIR FUEL PRIMARY SECONDARY INNER 10 07 BLUEBIRD 7182 6861 3231 or 3411 11 07 BLUEBIRD 7182 6861 3231 or 3411 12 07 BLUEBIRD 7182 6861 3231 or 3411 13 07 BLUEBIRD 7182 6861 3231 or 3411 14 08 BLUEBIRD 7182 6861 3231 or 3411 15 08 BLUEBIRD 7182 6861 3231 or 3411 20 03 BLUEBIRD 7182 6500 3231 or 3411 21 06 BLUEBIRD 1791XE 6861 6862 3231 or 3411 So, not all fields are full, and headings are split - can it still work? "Roger Govier" wrote: 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/f...es/pivot1.html -- Regards Roger Govier "partsman_ba" wrote in message ... 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? "partsman_ba" wrote: 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! |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Making a list of discrete numbers (names?) and occurences of e
Hi
For a Pivot Table to work, each column has to have a unique heading in the first row. It does not matter if there is data in every column of each row. From your posting, it is difficult to see your layout. -- Regards Roger Govier "partsman_ba" wrote in message ... What I would like on, say, Sheet2 would be: 1791XE 1 3231 or 3411 8 6500 1 6861 7 6862 1 7182 7 and if I add a new number to Sheet1, it shows up on my list "partsman_ba" wrote: Roger, Here's a small piece of what the top looks like: 4J FILTERS UNIT # YR/MAKE LUBE AIR FUEL PRIMARY SECONDARY INNER 10 07 BLUEBIRD 7182 6861 3231 or 3411 11 07 BLUEBIRD 7182 6861 3231 or 3411 12 07 BLUEBIRD 7182 6861 3231 or 3411 13 07 BLUEBIRD 7182 6861 3231 or 3411 14 08 BLUEBIRD 7182 6861 3231 or 3411 15 08 BLUEBIRD 7182 6861 3231 or 3411 20 03 BLUEBIRD 7182 6500 3231 or 3411 21 06 BLUEBIRD 1791XE 6861 6862 3231 or 3411 So, not all fields are full, and headings are split - can it still work? "Roger Govier" wrote: 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/f...es/pivot1.html -- Regards Roger Govier "partsman_ba" wrote in message ... 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? "partsman_ba" wrote: 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! |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Making a list of discrete numbers (names?) and occurences of e
Although, I think it's a good idea for each field to have a unique header, all
that's required is that each field have a header (non-empty cell). Roger Govier wrote: Hi For a Pivot Table to work, each column has to have a unique heading in the first row. It does not matter if there is data in every column of each row. From your posting, it is difficult to see your layout. -- Regards Roger Govier "partsman_ba" wrote in message ... What I would like on, say, Sheet2 would be: 1791XE 1 3231 or 3411 8 6500 1 6861 7 6862 1 7182 7 and if I add a new number to Sheet1, it shows up on my list "partsman_ba" wrote: Roger, Here's a small piece of what the top looks like: 4J FILTERS UNIT # YR/MAKE LUBE AIR FUEL PRIMARY SECONDARY INNER 10 07 BLUEBIRD 7182 6861 3231 or 3411 11 07 BLUEBIRD 7182 6861 3231 or 3411 12 07 BLUEBIRD 7182 6861 3231 or 3411 13 07 BLUEBIRD 7182 6861 3231 or 3411 14 08 BLUEBIRD 7182 6861 3231 or 3411 15 08 BLUEBIRD 7182 6861 3231 or 3411 20 03 BLUEBIRD 7182 6500 3231 or 3411 21 06 BLUEBIRD 1791XE 6861 6862 3231 or 3411 So, not all fields are full, and headings are split - can it still work? "Roger Govier" wrote: 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/f...es/pivot1.html -- Regards Roger Govier "partsman_ba" wrote in message ... 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? "partsman_ba" wrote: 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! -- Dave Peterson |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Making a list of discrete numbers (names?) and occurences of e
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,R OWS($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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "partsman_ba" wrote: What I would like on, say, Sheet2 would be: 1791XE 1 3231 or 3411 8 6500 1 6861 7 6862 1 7182 7 and if I add a new number to Sheet1, it shows up on my list "partsman_ba" wrote: Roger, Here's a small piece of what the top looks like: 4J FILTERS UNIT # YR/MAKE LUBE AIR FUEL PRIMARY SECONDARY INNER 10 07 BLUEBIRD 7182 6861 3231 or 3411 11 07 BLUEBIRD 7182 6861 3231 or 3411 12 07 BLUEBIRD 7182 6861 3231 or 3411 13 07 BLUEBIRD 7182 6861 3231 or 3411 14 08 BLUEBIRD 7182 6861 3231 or 3411 15 08 BLUEBIRD 7182 6861 3231 or 3411 20 03 BLUEBIRD 7182 6500 3231 or 3411 21 06 BLUEBIRD 1791XE 6861 6862 3231 or 3411 So, not all fields are full, and headings are split - can it still work? "Roger Govier" wrote: 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/f...es/pivot1.html -- Regards Roger Govier "partsman_ba" wrote in message ... 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? "partsman_ba" wrote: 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! |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Making a list of discrete numbers (names?) and occurences of e
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" wrote: 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,R OWS($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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "partsman_ba" wrote: What I would like on, say, Sheet2 would be: 1791XE 1 3231 or 3411 8 6500 1 6861 7 6862 1 7182 7 and if I add a new number to Sheet1, it shows up on my list "partsman_ba" wrote: Roger, Here's a small piece of what the top looks like: 4J FILTERS UNIT # YR/MAKE LUBE AIR FUEL PRIMARY SECONDARY INNER 10 07 BLUEBIRD 7182 6861 3231 or 3411 11 07 BLUEBIRD 7182 6861 3231 or 3411 12 07 BLUEBIRD 7182 6861 3231 or 3411 13 07 BLUEBIRD 7182 6861 3231 or 3411 14 08 BLUEBIRD 7182 6861 3231 or 3411 15 08 BLUEBIRD 7182 6861 3231 or 3411 20 03 BLUEBIRD 7182 6500 3231 or 3411 21 06 BLUEBIRD 1791XE 6861 6862 3231 or 3411 So, not all fields are full, and headings are split - can it still work? "Roger Govier" wrote: 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/f...es/pivot1.html -- Regards Roger Govier "partsman_ba" wrote in message ... 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? "partsman_ba" wrote: 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! |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Making a list of discrete numbers (names?) and occurences of e
welcome, good to hear it worked for you.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "partsman_ba" wrote in message ... 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! |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Making a list of discrete numbers (names?) and occurences of e
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., "Roger Govier" wrote: 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/f...es/pivot1.html -- Regards Roger Govier "partsman_ba" wrote in message ... 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? "partsman_ba" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I change a list of phone numbers into peoples names | New Users to Excel | |||
List Unique Occurences and Count | Excel Worksheet Functions | |||
unique occurences in list | Excel Worksheet Functions | |||
unique occurences in list | Excel Worksheet Functions | |||
making a list of numbers. | Excel Discussion (Misc queries) |