Feb 14 2018
04:40 AM
- last edited on
Jul 25 2018
11:03 AM
by
TechCommunityAP
Feb 14 2018
04:40 AM
- last edited on
Jul 25 2018
11:03 AM
by
TechCommunityAP
Hi,
I have an excel sheet with 3 columns. In column A there are categories, in column B there are the different devices from each category. In column C I want to list all diveces depending of a condition. Please have a look to examble excel sheet. I know that you can solf this with INDEX, VERGLEICH and ARRAY but I don't know how.
Thanks for your help.
Andreas
Feb 14 2018 06:21 AM
Andreas,
first delete the rows with the sub headers and fill in a column header for column A.
Then Press CTRL-SHIFT-L and filter in column A.
Feb 15 2018 12:26 AM
Hi Detlev,
but I need the subheaders, because I want too hide column A when the sheet was finished.
And how is the formula for column C?
Thanks
Feb 15 2018 01:19 AM
Then try this in C3:
=INDEX(B:B,AGGREGATE(15,6,ROW($A$3:$A$50)/($A$3:$A$50="Monitore"),ROWS(C$3:C3)))
Feb 15 2018 02:31 AM - edited Feb 15 2018 02:35 AM
I inserted the following formula (I hope it is correct for te german version), but I got the error #DIV/0!
=INDEX(B:B;SUMME(15;6;ZEILE($A$3:$A$50)/($A$3:$A$50="Monitore");ZEILE(C$3:C3)))
Feb 15 2018 03:12 AM
In German that would be:
=INDEX(B:B;AGGREGAT(15;6;ZEILE($A$3:$A$50)/($A$3:$A$50="Monitore");ZEILEN(C$3:C3)))
Feb 15 2018 04:26 AM
Hi Detlev,
Have many thanks. It works nearly perfect ;)
If I copied the formula for example to 20 rows, but in column B there are only 10results, then I get an error message from row eleven to twenty #ZAHL!
I can create a conditional formatting that #ZAHL! should printed white but exists there a better way?
Greetings Andreas
Feb 15 2018 09:30 AM
Alternatively you can wrap IFERROR() around your formula.
Feb 16 2018 12:41 AM
SolutionHi Detlev,
brilliant :) Hav emany thanks. For my examble this is the perfect working formula:
=WENNFEHLER(INDEX(L:L;AGGREGAT(15;6;ZEILE($K$3:$K$300)/($K$3:$K$300=$H$4);ZEILEN(K$3:K16)));"")This was my first question in such a community and it worked perfect. Your answers were very quick and they led to the goal. Have a nice weekend. Greetings Andreas
Feb 16 2018 02:30 AM
Thanks, Andreas. You're welcome.
If you prefer German speaking/writing support then there are several communities/forums you can join.
Feb 16 2018 12:41 AM
SolutionHi Detlev,
brilliant :) Hav emany thanks. For my examble this is the perfect working formula:
=WENNFEHLER(INDEX(L:L;AGGREGAT(15;6;ZEILE($K$3:$K$300)/($K$3:$K$300=$H$4);ZEILEN(K$3:K16)));"")This was my first question in such a community and it worked perfect. Your answers were very quick and they led to the goal. Have a nice weekend. Greetings Andreas