List of names from a column

Leesha
5StarLounger
Posts: 1112
Joined: 05 Feb 2010, 22:25

List of names from a column

Post by Leesha »

Hi,
I have a user that runs a very complex report in Excel every month and would like it formatted to run based on a macro. I can do most of it however the part that I'm running into trouble with is coming up with a list of names that are in a column (without using a pivot table). Column G, which is titled "Store Names" currently has 45 rows of data. There are 10 distinct store names in this report. This will vary from month to month as will the number of rows of data. I need to provide a list of the distinct stores by name on the spreadsheet, below the rows of data. I was going to start listing the distinct store names in cell B200 but I have no idea how to do this in code. Is it even possible?

EDIT: I found how to do this using the Advanced Filter and it will work in most of the areas of the report. The issue I'm running into now is that there is a section of the report that requires a unique list of stores based on ContractVersion. ContractVersion is store in column z. IE one unique list would contain the store names for contract numbers <3 and >4 and the other list would contain store names for contracts 3 and 4. Note: Contract 4 is not always in the report. This report will eventually have thousands of rows of data which is why they are trying to get it right now with only 45 rows.

Thanks!
Leesha

User avatar
HansV
Administrator
Posts: 71240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: List of names from a column

Post by HansV »

Which version of Office do you and your users have? Microsoft 365 has new functions that make this very easy.
Regards,
Hans

Leesha
5StarLounger
Posts: 1112
Joined: 05 Feb 2010, 22:25

Re: List of names from a column

Post by Leesha »

We are all using 365

User avatar
HansV
Administrator
Posts: 71240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: List of names from a column

Post by HansV »

Great!

In the first cell where you want the list of store names for contract versions 3 and 4, enter the formula

=UNIQUE(FILTER($G$2:$G$150,($Z$2:$Z$150>=3)*($Z$2:$Z$150<=4)))

In the first cell where you want the list of store names for all other contract versions, enter the formula

=UNIQUE(FILTER($G$2:$G$150,($Z$2:$Z$150<3)+($Z$2:$Z$150>4)))

Excel should automatically "spill" the formulas down as far as needed.
Regards,
Hans

Leesha
5StarLounger
Posts: 1112
Joined: 05 Feb 2010, 22:25

Re: List of names from a column

Post by Leesha »

Thank you Hans!! This is perfect!!!!
Have a great day!
Leesha

Leesha
5StarLounger
Posts: 1112
Joined: 05 Feb 2010, 22:25

Re: List of names from a column

Post by Leesha »

Thank you Hans!! This is perfect!!!! Question, is there a way to get rid of the zero in last row? when I go to delete it I get a message that says that it s part of the array and can't be deleted and the formula needs to be modifed.

Leesha

User avatar
HansV
Administrator
Posts: 71240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: List of names from a column

Post by HansV »

That 0 is caused by the empty rows in the ranges. If you "shrink" $G$2:$G$150 etc. to just the used rows, the 0 should disappear.
Alternatively, you could turn the data list into a table and refer to that, or use dynamic named ranges.
Regards,
Hans

Leesha
5StarLounger
Posts: 1112
Joined: 05 Feb 2010, 22:25

Re: List of names from a column

Post by Leesha »

OK sounds good! I appreciate the explanation!!

Leesha
5StarLounger
Posts: 1112
Joined: 05 Feb 2010, 22:25

Re: List of names from a column

Post by Leesha »

Hi Hans,
I wasn't sure if I should create a new post of put this question here since it relates to the same excel situation.

Now that the unique store names populate with the code you gave me earlier, I need to find the sum of the netsales / store. I'm using the following code: =SUMIF(A:A,I1001,Y:Y) where column A includes all the stores by name, i1001 is the cell containing the unique store name and column Y contains the netsales. It is working fine with the exception of one store name that has a contract 3, which should not be including in the total net sales for this portion of the report. [ContractVersion] is in column Z. Is there a formula for using the criteria of the store name and the contract version? I was so tickled that I figured out the formula I posted! Then the user pointed out the contract 3 issue. :-(

Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 71240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: List of names from a column

Post by HansV »

How about

=SUMIFS(Y:Y,A:A,I1001,Z:Z,"<>3",Z:Z,"<>4")
Regards,
Hans

Leesha
5StarLounger
Posts: 1112
Joined: 05 Feb 2010, 22:25

Re: List of names from a column

Post by Leesha »

You are so awesome!!! I tried revising the code for when the contract equals 3 or 4 got a 0.

I used =SUMIFS(AB:AB,D:D,L147,AC:AC,"=3",AC:AC,"=4"). What did I miss?

Thanks!

User avatar
HansV
Administrator
Posts: 71240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: List of names from a column

Post by HansV »

ALL conditions in SUMIFS must be satisfied. Since a contract version cannot be 3 and 4 simultaneously, you get 0.
Do it like this instead:

=SUMIFS(AB:AB,D:D,L147,AC:AC,3)+SUMIFS(AB:AB,D:D,L147,AC:AC,4)

or, slightly shorter

=SUM(SUMIFS(AB:AB,D:D,L147,AC:AC,{3,4}))
Regards,
Hans

Leesha
5StarLounger
Posts: 1112
Joined: 05 Feb 2010, 22:25

Re: List of names from a column

Post by Leesha »

Still not working. I'm uploading a stripped down version of the file with identifying info changed.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 71240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: List of names from a column

Post by HansV »

The formulas refer to the wrong columns; I don't know why you changed them from what we discussed earlier.

Hans.xlsx
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

Leesha
5StarLounger
Posts: 1112
Joined: 05 Feb 2010, 22:25

Re: List of names from a column

Post by Leesha »

Hi Hans,
I'm not sure why my info location changed unless it was when I was prepping the spreadsheet to upload. I soooooooooooo appreciate your help with this!
Thanks so much,
Leesha