Count unique values and create list based on these values

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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)+1>ROWS(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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
> i have just this code to remove blanks in my column. It comes up with a
#num!
> error
>
> =IF(ROW()-ROW(noblanks)+1>ROWS(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" <[email protected]> wrote in message
> > news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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)+1>ROWS(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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
> i have just this code to remove blanks in my column. It comes up with a
#num!
> error
>
> =IF(ROW()-ROW(noblanks)+1>ROWS(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" <[email protected]> wrote in message
> > news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> >
> >
> >