How do you get a list???
 

Hi,

Hoping somone can help me with this......................

I have a an excel sheet that i use very regularly with approximately 5000
rows in it. In one column there is a list of company names, there are
approximately 200 companies and the list of companies is always changing. I
have to analyse all the transactions of these 200 odd companies and use a
sumif formula below the rows which adds the total volume for each company
(some companies have up to 150 transactions in the week). In order to get
the Criteria list for the sumif, i have to copy the column, sort and delete
all instances where the company appears more than once. This takes me a
decent amount of time and there is always the risk that i accidently delete a
company meaning I have to start the process again.

Is there a way of writing a formula that produces a list of all variables
that appear ina particular column (no matter how many times they appear) eg:

Original List Result

Apple Apple
Banana Banana
Pear Orange
Orange Pear
Apple
Apple
Banana
Banana
Banana
Banana
Banana


It's simply a list of what you would see if you applied a filter.

Thanks very much for reading this far!!!

Hope someone can help,

Chris

How do you get a list???
 

Look at Debra Dalgleish's site for Advanced filter with unique records.
http://contextures.com/xladvfilter01.html#FilterUR

But depending on what you're doing, you may want to look into using
pivottables. You can get some very nice numeric summaries with just a little
work.

Chris Kingham wrote:

Hi,

Hoping somone can help me with this......................

I have a an excel sheet that i use very regularly with approximately 5000
rows in it. In one column there is a list of company names, there are
approximately 200 companies and the list of companies is always changing. I
have to analyse all the transactions of these 200 odd companies and use a
sumif formula below the rows which adds the total volume for each company
(some companies have up to 150 transactions in the week). In order to get
the Criteria list for the sumif, i have to copy the column, sort and delete
all instances where the company appears more than once. This takes me a
decent amount of time and there is always the risk that i accidently delete a
company meaning I have to start the process again.

Is there a way of writing a formula that produces a list of all variables
that appear ina particular column (no matter how many times they appear) eg:

Original List Result

Apple Apple
Banana Banana
Pear Orange
Orange Pear
Apple
Apple
Banana
Banana
Banana
Banana
Banana

It's simply a list of what you would see if you applied a filter.

Thanks very much for reading this far!!!

Hope someone can help,

Chris






--

Dave Peterson

How do you get a list???
 

Thanks so much, that works perfectly!

Cheers
Chris

"Dave Peterson" wrote:

Look at Debra Dalgleish's site for Advanced filter with unique records.
http://contextures.com/xladvfilter01.html#FilterUR

But depending on what you're doing, you may want to look into using
pivottables. You can get some very nice numeric summaries with just a little
work.

Chris Kingham wrote:

Hi,

Hoping somone can help me with this......................

I have a an excel sheet that i use very regularly with approximately 5000
rows in it. In one column there is a list of company names, there are
approximately 200 companies and the list of companies is always changing. I
have to analyse all the transactions of these 200 odd companies and use a
sumif formula below the rows which adds the total volume for each company
(some companies have up to 150 transactions in the week). In order to get
the Criteria list for the sumif, i have to copy the column, sort and delete
all instances where the company appears more than once. This takes me a
decent amount of time and there is always the risk that i accidently delete a
company meaning I have to start the process again.

Is there a way of writing a formula that produces a list of all variables
that appear ina particular column (no matter how many times they appear) eg:

Original List Result

Apple Apple
Banana Banana
Pear Orange
Orange Pear
Apple
Apple
Banana
Banana
Banana
Banana
Banana

It's simply a list of what you would see if you applied a filter.

Thanks very much for reading this far!!!

Hope someone can help,

Chris






--

Dave Peterson