Show a list of values from one column with a value match in another column
I cannot think of the right function or code to read the specific column down on each row to match Yes and return the value of each row on the A column to another specific cell.
For example:
A
B
C
D
E
F
1
Store
Apple
Pear
Orange
Banana
Lemon
2
Wal-Mart
Yes
No
No
Yes
Yes
3
Target
Yes
Yes
Yes
No
No
4
Kroger
No
No
Yes
No
Yes
5
Aldi
Yes
No
Yes
Yes
Yes
6
Tom's
Yes
Yes
No
Yes
No
Sample result:
Item
Orange
Stores
Target, Kroger Aldi
If I select Apple (A8), I want the value of each store with "Yes" on Apple (column) and return with "Wal-Mart, Target, Aldi, Tom's" on A9 cell. But Kroger will be ignored. If I change Apple to Pear, the search will change to a different column and search down then return with "Target, Tom's" in the A9 cell automatically.
Can anyone please assist me on that?
Last edited by Zorrie; 10-08-2023 at 10:19 PM.
Reason: SOLVED
Re: Show a list of values from one column with a value match in another column
Originally Posted by jeffreybrown
Is your version of Excel correct? If you have 365 you could use >> =TEXTJOIN(", ",,FILTER(A2:A6,INDEX(B2:F6,,MATCH(A8,B1:F1,0))="Yes"))
Wow, it does work very well. I did try to use TEXTJOIN and INDEX but I never thought that FILTER and MATCH functions do work too.
One more question before I am going to close this as Solved: How can I input the new line or return the value on the next row down from the original cell with that code if it is possible?
By the way, I tried to use formatted data but it won't let me somehow. Forgive me for that.
PS. Another question: How can you edit the code to check two or more fruits with "Yes" at the same time?
Last edited by Zorrie; 10-05-2023 at 03:00 PM.
Reason: Add another question
Re: Show a list of values from one column with a value match in another column
Yes, I already have the answer for Pear only in B9 and Orange only in B12. But I need to have the cell to display any store that has both Pear and Orange as TRUE.
There is a pear in Target and Tom's. (B9)
There is an orange in Target, Kroger, and Aldi. (B12)
and then to understand how to apply the Power Query....
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
Re: Show a list of values from one column with a value match in another column
Thanks for your effort but I want this working without addons because it will go into Google Spreadsheet.
You should make that clear up front so others don't waste their precious time doing something for you.
At this site it is about learning and not having someone do your work for you. My efforts were to expand your knowledge and abilities. If you had made your total needs known, I would have let this post fly by. Good Luck with your project.
Re: Show a list of values from one column with a value match in another column
Originally Posted by alansidman
You should make that clear up front so others don't waste their precious time doing something for you.
At this site, it is about learning and not having someone do your work for you. My efforts were to expand your knowledge and abilities. If you had made your total needs known, I would have let this post fly by. Good Luck with your project.
Okay, my apology for that. However, no one wants to ask me if I want to do the add-ons or not. I tried to get this small issue working out of my huge Excel/Spreadsheet project which I have been working on for the last couple of years.
Re: Show a list of values from one column with a value match in another column
However, no one wants to ask me if I want to do the add-ons or not.
Power Query is not an add in for 365. It is an integral part of Excel and found on the DATA tab. It is referred to there as Get and Transform Data as I noted in Post #15.
Re: Show a list of values from one column with a value match in another column
Originally Posted by FlameRetired
Yeah OK I think I see it now. Try
Yeah, it does work.
To prevent any error message from being displayed, I had to add the IFERROR function with "None found" in case two different items don't have the same store. But I tried to put the same item in both B8 and B11, it shows the error result (#VALUE!).
Instead of #VALUE! error, do I have to use some kind of function to check if B8 and B11 are the exact same, then B15 would show the result without any kind of error?
Re: Show a list of values from one column with a value match in another column
Rather than drip feeding us new information please show us the full range of variables you anticipate using the and the specific counts of "Yes" that qualify a store.
Re: Show a list of values from one column with a value match in another column
After the code for two different items is successful with people's help here, all I do is add the third item and compare three different items to see which store has all of them. But the result shows an error. I am not sure what I have done wrong.
Also, as I said before, I had to add an IFERROR function with "None found" if three different items have no common store. I tried to get this working if two or three different cells have the same item, then the result would be the list of store(s) if there are without displaying the #VALUE! error.
I know that it seems a very tall order. But I am struggling to get this code working as I am doing my best.
Re: Show a list of values from one column with a value match in another column
Originally Posted by Zorrie
It may work in Excel but not in Google Spreadsheet. So, that's why I want it work well in both Excel and Spreadsheet.
OK.
But I've already finished writing the VBA code for the Store function.
I'll still post it in case someone needs it for their project.
=Store(Data_Range, lookup_cell)
In B9:
=store($A$1:$F$6,B8)
PHP Code:
Option Explicit Private Function Store(ByVal rng As Range, ce As Range) As String Dim i&, j&, s, st, rng2 Dim dic As Object Set dic = CreateObject("Scripting.Dictionary") rng2 = rng.Value For Each s In Split(ce, ",") For j = 2 To UBound(rng2, 2) If rng2(1, j) = Trim(s) Then For i = 2 To UBound(rng2) If rng2(i, j) = "Yes" Then If Not dic.exists(i) Then st = IIf(st = "", "", st & ", ") & rng2(i, 1) dic.Add i, "" End If End If Next End If Next Next Store = st Set dic = Nothing End Function
Re: Show a list of values from one column with a value match in another column
Originally Posted by FlameRetired
I used the set up in the attached to add the 3rd item......
It does work well. As I cleaned up the unnecessary empty cells and sorted the items in a single row with the list of stores with "Yes" below each item, I added your code to the cell that displayed the list of selected items and tried to use your code to show the list of specific stores which have all of the selected items. After I edited that code with the correct cells, it did not show any stores as it should be. Did I overlook the code somewhere in the F9 cell?
Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
MS-Off Ver
MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
Posts
80,916
Re: Show a list of values from one column with a value match in another column
There's a file attached to post #35 (second to bottom line of the post).
Ali Enthusiastic self-taught user of MS Excel who's always learning! Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish. Forum Rules (updated August 2023): please read them here.
Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
MS-Off Ver
MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
Posts
80,916
Re: Show a list of values from one column with a value match in another column
Or maybe this?
=LET(f,FILTER(B2:H6,ISNUMBER(MATCH(B1:H1,B8:D8,0))),b,BYROW(f,LAMBDA(r,SUMPRODUCT((r="Yes")*(r<>""))=COUNTA(B8:D8))),IFERROR(TEXTJOIN(", ",,FILTER(A2:A6,b)),"No Store Qualifies"))
Re: Show a list of values from one column with a value match in another column
Originally Posted by Zorrie
........Did I overlook the code somewhere in the F9 cell?
I don't know. The solution I offered was heavily dependent upon the layout. Will you be staying with this new layout? If so it will take me a while to solve this new one ... which will also be heavily dependent upon layout.
Re: Show a list of values from one column with a value match in another column
Originally Posted by AliGW
@Zorrie - some feedback would be appreciated. Did my suggestion work for you or not?
Yes, it does work in the example of my work. However, I tried converting your code to my work with multiple sheets. The result shows nothing. I did check through the edited code carefully many times but nothing happened.
Also, I test to put the same fruit in Item #1, #2, and #3; the result shows nothing. It should show the list of places.
Would your code be working well in Google Spreadsheet as it should be in Excel?
Re: Show a list of values from one column with a value match in another column
Originally Posted by FlameRetired
I don't know. The solution I offered was heavily dependent upon the layout. Will you be staying with this new layout? If so it will take me a while to solve this new one ... which will also be heavily dependent upon layout.
Re: Show a list of values from one column with a value match in another column
Originally Posted by FlameRetired
I worked with your latest upload. In the attached I filled out row 8 with drop downs to column H. This allows the solution to work with up to 7...
@FlameRetired,
Oddly, with three selected items, it does work in my real work. If I selected two items or fewer OR two or more same items, the error message shows at my end. The error said, "No matches are found in FILTER evaluation." if I selected two items or fewer OR selected two or three same items. Same for the empty cells.
@AliGW,
I tried to use your code in my real work but it only works with three selected items. However, it won't work with two selected items or fewer. Not even with empty cells.
That is really a big scratch on the back of my head.
Re: Show a list of values from one column with a value match in another column
My previous was not accounting for duplicates or the fact that UNIQUE requires a TRUE argument when applied to columns which has row wise arrays (all comma separated values).
My bad. My apologies.
Re: Show a list of values from one column with a value match in another column
Originally Posted by FlameRetired
My previous was not accounting for duplicates or the fact that UNIQUE requires a TRUE argument when applied to columns which has row wise arrays (all comma separated values)....
Yes, yes, yes, yes, yes, you did it! It's working exactly as I want it. I had to replace ", " with CHAR(10) in TEXTJOIN function but it stretched the cell down with the list of stores in a new line. How can I get the list of stores in each row of the cell below just like the single item that displayed the list of stores below?
LinkBack URL
About LinkBacks
Show a list of values from one column with a value match in another column
Register To Reply
Originally Posted by jeffreybrown
Bookmarks