Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
vipa2000
 
Posts: n/a
Default 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
  #2   Report Post  
Alan Beban
 
Posts: n/a
Default

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

  #3   Report Post  
RagDyer
 
Posts: n/a
Default

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


  #4   Report Post  
vipa2000
 
Posts: n/a
Default

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



  #5   Report Post  
RagDyeR
 
Posts: n/a
Default

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







  #6   Report Post  
vipa2000
 
Posts: n/a
Default

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






  #7   Report Post  
vipa2000
 
Posts: n/a
Default

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






  #8   Report Post  
RagDyer
 
Posts: n/a
Default

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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Create a legend of values [email protected] Excel Discussion (Misc queries) 3 July 14th 05 08:07 AM
create list of unique values from a column with repeated values? Chad Schaben Excel Worksheet Functions 1 July 8th 05 10:25 PM
Populate a column by extracting unique values from another column? Mike Palmer Excel Worksheet Functions 2 June 10th 05 03:21 PM
create a list of worksheet names (from a single folder, or open files) Drew Excel Discussion (Misc queries) 2 April 15th 05 04:58 PM
How can I create a list that skips zero values? S.K.S. Excel Worksheet Functions 3 February 28th 05 02:44 AM


All times are GMT +1. The time now is 09:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"