list formula

shodan

Active Member
Joined
Jul 6, 2005
Messages
486
Hi,

Does excel has a kind of a list formula?

I mean, suppose you have a kind of a hierarchy in colums A Containing all different kind a data. Than in column B you have data. Let's say that I would like to list the data from column A where there is an "X" in column B.
The list may be in seperate cells or just as a list in one cell.
I can think of a vlookup, but that would only give me one item, or a countif but that would not retieve the values from column A

Regards,
Shodan
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
shodan

Have a look at Data|Filter|AutoFilter and filter on column B for "X" values.
 
Upvote 0
Hey thanks,

yes I know that, but I was looking an excel function. Not even a vba code. I was exploring some possibilities for a file that I want to set up.

so the thing I'm really looking for is some kind of formula that can list values (in one or multiple cells) if a certain condition is fullfilled.

regards
 
Upvote 0
OK, try this.

D1 (copied down): =B1&COUNTIF(B$1:B1,B1)
(Column D could be hidden if required)

E3: =COUNTIF(B1:B10,E2)

F1 (copied down to F10):
=IF(ROW()<=$E$3,INDEX($A$1:$A$10,MATCH($E$2&ROW(),$D$1:$D$10,0)),"")

If the lists do not start in row 1, some adjustments to the formulas would be required, but let's see if this looks like being any use.
Mr Excel.xls
ABCDEF
1Item 1AA1Column B ValueItem 2
2Item 2XX1XItem 3
3Item 3XX25Item 6
4Item 4AA2Item 8
5Item 5BB1Item 9
6Item 6XX3 
7Item 7BB2 
8Item 8XX4 
9Item 9XX5 
10Item 10AA3 
Sheet1
 
Upvote 0
Hey Peter,

Indeed, this will work, thanks, but it limits layout possibilities because I always need to have as many filled formula rows as items may appear. But that could be solved if I put this on another sheet.

But what really would be ideal for me now is something like the countif, where it does not returns the number of counts but the values of the corresponding counts.

Hey sorry man, I don't want to come over like I don't except what you are saying, It is not, I appreciate your help. it is just that I would like to explore all possiblities.

Thanks a lot.
 
Upvote 0
But that could be solved if I put this on another sheet.
There should be no problem having the formulas on another sheet.
But what really would be ideal for me now is something like the countif, where it does not returns the number of counts but the values of the corresponding counts.
Can you give a small sample of data and the expected results?
 
Upvote 0
like this:
Book2
ABCDEFG
1Item 1A
2Item 2B3In stead of the the number I would
3Item 3Xto see the values representing X
4Item 4XExpected result:
5Item 5AItem 3; Item4; Item6
6Item 6X
7Item 7B
8Item 8D
9Item 9A
10Item 10Z
Sheet1


An intermediate solution would be using your previous formula , put it on another sheet and concatenate the results.

thanks
 
Upvote 0
shodan

Is this any use?

1. I have moved my list of results to column G.
2. The formula in G1 stays the same, but G2 (copied down) is changed to =IF(ROW()<=$F$3,"; "&INDEX($A$1:$A$10,MATCH($E$2&ROW(),$D$1:$D$10,0)),"")
3. The COUNTIF formula is moved from E3 to F3. This way columns F and G (and D) can be hidden if required (no need to move to another sheet unless you want to for some other reason)
4. The formula in E3 is =G1&G2&G3&G4&G5&G6&G7&G8&G9&G10

Clearly this concatenation in E3 is only feasible if the data range is reasonable small. If large, you may want a VBA solution.
Mr Excel.xls
ABCDEFG
1Item 1AA1Column B ValueItem 2
2Item 2XX1X; Item 3
3Item 3XX2Item 2; Item 3; Item 6; Item 8; Item 95; Item 6
4Item 4AA2; Item 8
5Item 5BB1; Item 9
6Item 6XX3 
7Item 7BB2 
8Item 8XX4 
9Item 9XX5 
10Item 10AA3 
List by Column B
 
Upvote 0
Yes this is also how i would do it based on your solution. to bad that there isn't a kind of LISTIF formula, but I guess that maybe is more a querry statement.

thanks,
Shodan
 
Upvote 0
While we are on the "too bad there isn't a..." theme:

Too bad there isn'a a CONCATENATE(A1:A20,",") type function that would concatenate a range of cells (or values) using the second argument as the joiner. It might be useful in situations like yours.
 
Upvote 0

Forum statistics

Threads
1,214,437
Messages
6,119,522
Members
448,903
Latest member
StephMJ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top