list formula

[shodan]

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

 

[Peter_SSs]

shodan

Have a look at Data|Filter|AutoFilter and filter on column B for "X" values.

 

[shodan]

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

 

[Peter_SSs]

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
	A	B	C	D	E	F
1	Item 1	A		A1	Column B Value	Item 2
2	Item 2	X		X1	X	Item 3
3	Item 3	X		X2	5	Item 6
4	Item 4	A		A2		Item 8
5	Item 5	B		B1		Item 9
6	Item 6	X		X3
7	Item 7	B		B2
8	Item 8	X		X4
9	Item 9	X		X5
10	Item 10	A		A3
Sheet1

 

[shodan]

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.

 

[Peter_SSs]

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?

 

[shodan]

like this:

Book2
	A	B	C	D	E	F	G
1	Item 1	A
2	Item 2	B	3	In stead of the the number I would
3	Item 3	X		to see the values representing X
4	Item 4	X	Expected result:
5	Item 5	A	Item 3; Item4; Item6
6	Item 6	X
7	Item 7	B
8	Item 8	D
9	Item 9	A
10	Item 10	Z
Sheet1

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

thanks

 

[Peter_SSs]

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
	A	B	C	D	E	F	G
1	Item 1	A		A1	Column B Value		Item 2
2	Item 2	X		X1	X		; Item 3
3	Item 3	X		X2	Item 2; Item 3; Item 6; Item 8; Item 9	5	; Item 6
4	Item 4	A		A2			; Item 8
5	Item 5	B		B1			; Item 9
6	Item 6	X		X3
7	Item 7	B		B2
8	Item 8	X		X4
9	Item 9	X		X5
10	Item 10	A		A3
List by Column B

 

[shodan]

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

 

[Peter_SSs]

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.