|
Count unique values and create list based on these values
I have the below data. Firstly, I don't want to use excels autofilters. My
data changes monthly. I want 2 worksheets, one that contains data pasted in by users, which will vary in length and the other worksheet functioning as a report sheet interrogating the data on spreadsheet 1. On worksheet 1 column 1 will have multiple entires but with many duplicates. I want the report sheet to count the number of unique values and count them, and now the tricky bit, generate a list displaying this data. I have in other reports set my column ranges as an example to a2:a30000. 1 2 120209 CONELECT 120209 CONMISGS 120209 CONMISGS 120215 CONBATT 120215 CONCAIRO -- Regards vipa |
Perhaps a bit more detail on what you mean by "a list displaying this
data" would enable someone to readily give meaningful help. Alan Beban vipa2000 wrote: I have the below data. Firstly, I don't want to use excels autofilters. My data changes monthly. I want 2 worksheets, one that contains data pasted in by users, which will vary in length and the other worksheet functioning as a report sheet interrogating the data on spreadsheet 1. On worksheet 1 column 1 will have multiple entires but with many duplicates. I want the report sheet to count the number of unique values and count them, and now the tricky bit, generate a list displaying this data. I have in other reports set my column ranges as an example to a2:a30000. 1 2 120209 CONELECT 120209 CONMISGS 120209 CONMISGS 120215 CONBATT 120215 CONCAIRO |
Check out Chip Pearson's web site:
http://www.cpearson.com/excel/topic.htm Scroll down to the D's and check out all the pages on "Duplicates", where you can find exactly what you need. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "vipa2000" wrote in message ... I have the below data. Firstly, I don't want to use excels autofilters. My data changes monthly. I want 2 worksheets, one that contains data pasted in by users, which will vary in length and the other worksheet functioning as a report sheet interrogating the data on spreadsheet 1. On worksheet 1 column 1 will have multiple entires but with many duplicates. I want the report sheet to count the number of unique values and count them, and now the tricky bit, generate a list displaying this data. I have in other reports set my column ranges as an example to a2:a30000. 1 2 120209 CONELECT 120209 CONMISGS 120209 CONMISGS 120215 CONBATT 120215 CONCAIRO -- Regards vipa |
Thanks for that RagDyer. On the spreadsheet where the data was I inserted a
column and put the following code ina cell. =IF(COUNTIF($A$2:A2,A2)=1,A2,""). this worked fine. As originally specified I wanted the code to be on a separate worksheet, but obviously looking at the data worksheet. i used this code =IF(COUNTIF(sheet1!$A$2:A2,A2)=1,A2,""). all it returns is zeros's. tried a few things. Any ideas? -- Regards vipa "RagDyer" wrote: Check out Chip Pearson's web site: http://www.cpearson.com/excel/topic.htm Scroll down to the D's and check out all the pages on "Duplicates", where you can find exactly what you need. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "vipa2000" wrote in message ... I have the below data. Firstly, I don't want to use excels autofilters. My data changes monthly. I want 2 worksheets, one that contains data pasted in by users, which will vary in length and the other worksheet functioning as a report sheet interrogating the data on spreadsheet 1. On worksheet 1 column 1 will have multiple entires but with many duplicates. I want the report sheet to count the number of unique values and count them, and now the tricky bit, generate a list displaying this data. I have in other reports set my column ranges as an example to a2:a30000. 1 2 120209 CONELECT 120209 CONMISGS 120209 CONMISGS 120215 CONBATT 120215 CONCAIRO -- Regards vipa |
You say it returned zeroes?!?!
It should have returned *blank* cells ... N0? But, then again, I don't know what you have in Column A of the Sheet where you've entered this formula. Your formula is looking in Sheet1 for the data list, *BUT* looking in the sheet containing the formula for *both* the matching criteria *and* the cell to be returned. Anyway, with all your data in Sheet1, the formula should read: =IF(COUNTIF(Sheet1!$A$2:A2,Sheet1!A2)=1,Sheet1!A2, "") -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "vipa2000" wrote in message ... Thanks for that RagDyer. On the spreadsheet where the data was I inserted a column and put the following code ina cell. =IF(COUNTIF($A$2:A2,A2)=1,A2,""). this worked fine. As originally specified I wanted the code to be on a separate worksheet, but obviously looking at the data worksheet. i used this code =IF(COUNTIF(sheet1!$A$2:A2,A2)=1,A2,""). all it returns is zeros's. tried a few things. Any ideas? -- Regards vipa "RagDyer" wrote: Check out Chip Pearson's web site: http://www.cpearson.com/excel/topic.htm Scroll down to the D's and check out all the pages on "Duplicates", where you can find exactly what you need. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "vipa2000" wrote in message ... I have the below data. Firstly, I don't want to use excels autofilters. My data changes monthly. I want 2 worksheets, one that contains data pasted in by users, which will vary in length and the other worksheet functioning as a report sheet interrogating the data on spreadsheet 1. On worksheet 1 column 1 will have multiple entires but with many duplicates. I want the report sheet to count the number of unique values and count them, and now the tricky bit, generate a list displaying this data. I have in other reports set my column ranges as an example to a2:a30000. 1 2 120209 CONELECT 120209 CONMISGS 120209 CONMISGS 120215 CONBATT 120215 CONCAIRO -- Regards vipa |
RagDyer thank you. Now is there a way for it to count the number of records
relating to each , it has generated a list with blank in and I was going to use the Cpearson code to remove the blanks. Would I be better running the blank removal code, to generate a concise list and then putting code in the cells adjacent to this list that would use some kind of count function example below col c 120260 if cell c1= not null count the columnA on sheet1 looking for values =c1 on this worksheet i.e 120260 -- Regards vipa "RagDyeR" wrote: You say it returned zeroes?!?! It should have returned *blank* cells ... N0? But, then again, I don't know what you have in Column A of the Sheet where you've entered this formula. Your formula is looking in Sheet1 for the data list, *BUT* looking in the sheet containing the formula for *both* the matching criteria *and* the cell to be returned. Anyway, with all your data in Sheet1, the formula should read: =IF(COUNTIF(Sheet1!$A$2:A2,Sheet1!A2)=1,Sheet1!A2, "") -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "vipa2000" wrote in message ... Thanks for that RagDyer. On the spreadsheet where the data was I inserted a column and put the following code ina cell. =IF(COUNTIF($A$2:A2,A2)=1,A2,""). this worked fine. As originally specified I wanted the code to be on a separate worksheet, but obviously looking at the data worksheet. i used this code =IF(COUNTIF(sheet1!$A$2:A2,A2)=1,A2,""). all it returns is zeros's. tried a few things. Any ideas? -- Regards vipa "RagDyer" wrote: Check out Chip Pearson's web site: http://www.cpearson.com/excel/topic.htm Scroll down to the D's and check out all the pages on "Duplicates", where you can find exactly what you need. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "vipa2000" wrote in message ... I have the below data. Firstly, I don't want to use excels autofilters. My data changes monthly. I want 2 worksheets, one that contains data pasted in by users, which will vary in length and the other worksheet functioning as a report sheet interrogating the data on spreadsheet 1. On worksheet 1 column 1 will have multiple entires but with many duplicates. I want the report sheet to count the number of unique values and count them, and now the tricky bit, generate a list displaying this data. I have in other reports set my column ranges as an example to a2:a30000. 1 2 120209 CONELECT 120209 CONMISGS 120209 CONMISGS 120215 CONBATT 120215 CONCAIRO -- Regards vipa |
i have just this code to remove blanks in my column. It comes up with a #num!
error =IF(ROW()-ROW(noblanks)+1ROWS(blanks)- COUNTBLANK(blanks),"",INDIRECT(ADDRESS(SMALL( (IF(blanks<"",ROW(blanks),ROW()+ROWS(blanks))), ROW()-ROW(noblanks)+1),COLUMN(blanks),4))) any ideas? -- Regards vipa "RagDyeR" wrote: You say it returned zeroes?!?! It should have returned *blank* cells ... N0? But, then again, I don't know what you have in Column A of the Sheet where you've entered this formula. Your formula is looking in Sheet1 for the data list, *BUT* looking in the sheet containing the formula for *both* the matching criteria *and* the cell to be returned. Anyway, with all your data in Sheet1, the formula should read: =IF(COUNTIF(Sheet1!$A$2:A2,Sheet1!A2)=1,Sheet1!A2, "") -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "vipa2000" wrote in message ... Thanks for that RagDyer. On the spreadsheet where the data was I inserted a column and put the following code ina cell. =IF(COUNTIF($A$2:A2,A2)=1,A2,""). this worked fine. As originally specified I wanted the code to be on a separate worksheet, but obviously looking at the data worksheet. i used this code =IF(COUNTIF(sheet1!$A$2:A2,A2)=1,A2,""). all it returns is zeros's. tried a few things. Any ideas? -- Regards vipa "RagDyer" wrote: Check out Chip Pearson's web site: http://www.cpearson.com/excel/topic.htm Scroll down to the D's and check out all the pages on "Duplicates", where you can find exactly what you need. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "vipa2000" wrote in message ... I have the below data. Firstly, I don't want to use excels autofilters. My data changes monthly. I want 2 worksheets, one that contains data pasted in by users, which will vary in length and the other worksheet functioning as a report sheet interrogating the data on spreadsheet 1. On worksheet 1 column 1 will have multiple entires but with many duplicates. I want the report sheet to count the number of unique values and count them, and now the tricky bit, generate a list displaying this data. I have in other reports set my column ranges as an example to a2:a30000. 1 2 120209 CONELECT 120209 CONMISGS 120209 CONMISGS 120215 CONBATT 120215 CONCAIRO -- Regards vipa |
On Sheet2,
Place the "Extracting" Countif() formula in Column A, Place the "Remove Blanks" *array* formula in Column B, And the "Counting" Countif() formula in Column C. That way, all you have to do is paste new data into Column A of Sheet1, and you'll *automatically* get your desired return on Sheet2. As far as the trouble you're having with the "Remove Blanks" formula; Have you named all your ranges correctly and consistently? Have you remembered that the formula is an *array* formula? -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "vipa2000" wrote in message ... i have just this code to remove blanks in my column. It comes up with a #num! error =IF(ROW()-ROW(noblanks)+1ROWS(blanks)- COUNTBLANK(blanks),"",INDIRECT(ADDRESS(SMALL( (IF(blanks<"",ROW(blanks),ROW()+ROWS(blanks))), ROW()-ROW(noblanks)+1),COLUMN(blanks),4))) any ideas? -- Regards vipa "RagDyeR" wrote: You say it returned zeroes?!?! It should have returned *blank* cells ... N0? But, then again, I don't know what you have in Column A of the Sheet where you've entered this formula. Your formula is looking in Sheet1 for the data list, *BUT* looking in the sheet containing the formula for *both* the matching criteria *and* the cell to be returned. Anyway, with all your data in Sheet1, the formula should read: =IF(COUNTIF(Sheet1!$A$2:A2,Sheet1!A2)=1,Sheet1!A2, "") -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "vipa2000" wrote in message ... Thanks for that RagDyer. On the spreadsheet where the data was I inserted a column and put the following code ina cell. =IF(COUNTIF($A$2:A2,A2)=1,A2,""). this worked fine. As originally specified I wanted the code to be on a separate worksheet, but obviously looking at the data worksheet. i used this code =IF(COUNTIF(sheet1!$A$2:A2,A2)=1,A2,""). all it returns is zeros's. tried a few things. Any ideas? -- Regards vipa "RagDyer" wrote: Check out Chip Pearson's web site: http://www.cpearson.com/excel/topic.htm Scroll down to the D's and check out all the pages on "Duplicates", where you can find exactly what you need. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "vipa2000" wrote in message ... I have the below data. Firstly, I don't want to use excels autofilters. My data changes monthly. I want 2 worksheets, one that contains data pasted in by users, which will vary in length and the other worksheet functioning as a report sheet interrogating the data on spreadsheet 1. On worksheet 1 column 1 will have multiple entires but with many duplicates. I want the report sheet to count the number of unique values and count them, and now the tricky bit, generate a list displaying this data. I have in other reports set my column ranges as an example to a2:a30000. 1 2 120209 CONELECT 120209 CONMISGS 120209 CONMISGS 120215 CONBATT 120215 CONCAIRO -- Regards vipa |
| All times are GMT +1. The time now is 11:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com