List of names from a column
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
List of names from a column
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
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
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: List of names from a column
Which version of Office do you and your users have? Microsoft 365 has new functions that make this very easy.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: List of names from a column
We are all using 365
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: List of names from a column
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.
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: List of names from a column
Thank you Hans!! This is perfect!!!!
Have a great day!
Leesha
Have a great day!
Leesha
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: List of names from a column
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
Leesha
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: List of names from a column
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.
Alternatively, you could turn the data list into a table and refer to that, or use dynamic named ranges.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: List of names from a column
OK sounds good! I appreciate the explanation!!
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: List of names from a column
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
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
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: List of names from a column
How about
=SUMIFS(Y:Y,A:A,I1001,Z:Z,"<>3",Z:Z,"<>4")
=SUMIFS(Y:Y,A:A,I1001,Z:Z,"<>3",Z:Z,"<>4")
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: List of names from a column
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!
I used =SUMIFS(AB:AB,D:D,L147,AC:AC,"=3",AC:AC,"=4"). What did I miss?
Thanks!
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: List of names from a column
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}))
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}))
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: List of names from a column
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.
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: List of names from a column
The formulas refer to the wrong columns; I don't know why you changed them from what we discussed earlier.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: List of names from a column
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
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