Show working-instructions depending on GHS-hazard-pictograms

**mrdaave** · 10-04-2023, 06:19 AM

Dear members of the forum,

I am trying to solve a quite complicated idea (for me) and hope to find some inputs for this here:

The idea is to make a tool, where you can choose between different GHS-hazard-pictograms for a product (max 5 per product) and then show in which rooms (they have different safety levels) the product can be handled.
All of the GHS-hazard-pictograms, rooms and B-Terms (I will explain later) are stored in a database.
Sometimes these products can be handled in a room but need additional safety precautions. Those are numbers from 1-11, labeled next to the room in the database. This information should be shown next to the rooms where it can be handled.

Last part: Almost all of the GHS-hazard-pictograms have additional requirements (B-Terms), which are stored in a separate database. Depending on the chosen GHS-hazard-pictograms it should be shown which additional requirements are necessary. The B-Terms sometimes change if the product is solid or liquid, so this has to be taken into account.

To explain it better, I made a sample workbook.

Because its just an idea, please feel free for new arrangements in the database etc.

Thank you in advance for your help and best regards,

David

**mrdaave** · 10-05-2023, 01:37 AM

Does somebody has an idea?
Every help is greatly appreciated!

**mrdaave** · 10-05-2023, 02:02 AM

For the first part I think I am almost there:

With:

=IF(B4='Database'!A2:A88;IF('Database!'B2:B88="Yes";"'Database'!B1;""))

This is just for one GHS-hazard-symbol and one room. And it shows all the rooms with FALSE for not true and nothing for true.
I would like to show just the rooms with the additional information in the according box.

Thank you in advance and best regards,

David

**mrdaave** · 10-05-2023, 02:26 AM

I got a little bit further to the desired solution:

With:

=IF(B4='Database'!A2:A88;IF('Database!'B2:B88="Yes";"'Database'!B1;'Database'!B1);"")

In this case I somehow have the same output for False and True, but it seems to show if the first GHS-hazard-symbol can be handled in the first room.
The problem is, that I get a list with Room 1 and a lot of blank cells which leads to a overflow in the box...

**AliGW** · 10-05-2023, 03:11 AM

There is a problem in that D4 and F4 do not exist on the Database sheet. That said, if you change A54 on Database to Reizend, Auge 2B, you can test this:

=LET(f,FILTER(Database!B2:U88,(Database!A2:A88=B4)+(Database!A2:A88=D4)+(Database!A2:A88=F4)+(Database!A2:A88=H4)+(Database!A2:A88=J4)),b,BYCOL(f,LAMBDA(c,SUMPRODUCT((c="Yes")*(c<>""))=ROWS(f))),TRANSPOSE(FILTER(Database!B1:U1,b=TRUE)))

=LET(f;FILTER(Database!B2:U88;(Database!A2:A88=B4)+(Database!A2:A88=D4)+(Database!A2:A88=F4)+(Database!A2:A88=H4)+(Database!A2:A88=J4));b;BYCOL(f;LAMBDA(c;SUMMENPRODUKT((c="Yes")*(c<>""))=ZEILEN(f)));MTRANS(FILTER(Database!B1:U1;b=WAHR)))

It only gets you partway there, but one step at a time!

**mrdaave** · 10-05-2023, 04:57 AM

Ali, thank you for your answer!

For now I just got #CALC! as an answer.
Excel told me that this is due to the array or the lambda.

But I´m still trying - fingers crossed.

Thank you,

David

**AliGW** · 10-05-2023, 05:03 AM

That should not be the case - did you open the workbook I attached? You should see this:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	L
8	AliGW
9	Room 1
10	Room 2
11	Room 3
12	Room 4

Sheet: Search

**mrdaave** · 10-05-2023, 06:07 AM

Your workbook is working - if I implement it to my sheet, i get #CALK!

**mrdaave** · 10-05-2023, 06:16 AM

New learning: #CALK! just means that there was no match in the whole database.
If I change one GHS-hazard-symbol to Explosive, I also get #CALK!

Is it possible to change that to: "Not possible to be handled in any room"?

**AliGW** · 10-05-2023, 06:32 AM

Of course!

=LET(f,FILTER(Database!B2:U88,(Database!A2:A88=B4)+(Database!A2:A88=D4)+(Database!A2:A88=F4)+(Database!A2:A88=H4)+(Database!A2:A88=J4)),b,BYCOL(f,LAMBDA(c,SUMPRODUCT((c="Yes")*(c<>""))=ROWS(f))),IFERROR(TRANSPOSE(FILTER(Database!B1:U1,b=TRUE)),"No Room Suitable"))

=LET(f;FILTER(Database!B2:U88;(Database!A2:A88=B4)+(Database!A2:A88=D4)+(Database!A2:A88=F4)+(Database!A2:A88=H4)+(Database!A2:A88=J4));b;BYCOL(f;LAMBDA(c;SUMMENPRODUKT((c="Yes")*(c<>""))=ZEILEN(f)));WENNFEHLER(MTRANS(FILTER(Database!B1:U1;b=WAHR));"Kein Passendes Zimmer"))

**mrdaave** · 10-05-2023, 07:02 AM

Oh Ali, you are amazing! Thank you very much for your help so far!
And thank you for the german translation

**AliGW** · 10-05-2023, 07:04 AM

Kein Problem - gern geschehen.

I don't have time just at the moment to look into it any further, but someone else will chip in.

**mrdaave** · 10-05-2023, 07:12 AM

You do a really great job here! I have to give some reputation to someone else before I can send some to you - but I wont forget :D

And I cant wait to finish this idea - so far I think we have a solid base!

**AliGW** · 10-05-2023, 07:56 AM

Got a bit further:

=LET(f,FILTER(Database!B2:U88,(Database!A2:A88=B4)+(Database!A2:A88=D4)+(Database!A2:A88=F4)+(Database!A2:A88=H4)+(Database!A2:A88=J4)),b,BYCOL(f,LAMBDA(c,SUMPRODUCT((c="Yes")*(c<>""))=ROWS(f))),r,TRANSPOSE(FILTER(Database!B1:U1,b=TRUE)),h,HSTACK(r,TRANSPOSE(FILTER(DROP(f,,1),DROP(b,,-1)=TRUE))),v,IFNA(VLOOKUP(TRANSPOSE(FILTER(DROP(f,,1),DROP(b,,-1)=TRUE)),Database!X4:Y14,2,0),""),IFERROR(HSTACK(r,BYROW(v,LAMBDA(r,TEXTJOIN(", ",1,r)))),"No Room Available"))

=LET(f;FILTER(Database!B2:U88;(Database!A2:A88=B4)+(Database!A2:A88=D4)+(Database!A2:A88=F4)+(Database!A2:A88=H4)+(Database!A2:A88=J4));b;BYCOL(f;LAMBDA(c;SUMMENPRODUKT((c="Yes")*(c<>""))=ZEILEN(f)));r;MTRANS(FILTER(Database!B1:U1;b=WAHR));h;HSTACK(r;MTRANS(FILTER(DROP(f;;1);DROP(b;;-1)=WAHR)));v;WENNNV(SVERWEIS(MTRANS(FILTER(DROP(f;;1);DROP(b;;-1)=WAHR));Database!X4:Y14;2;0);"");WENNFEHLER(HSTACK(r;BYROW(v;LAMBDA(r;TEXTVERKETTEN(", ";1;r))));"Kein Passendes Zimmer"))

I am aware of the duplication - that needs to be resolved.

**mrdaave** · 10-05-2023, 08:25 AM

But that looks amazing so far! I've tried to find a solution to match the additional information as well, but i wasn`t successful.
Your solution works for me as well - I have also implemented it already

**AliGW** · 10-05-2023, 09:16 AM

I'm not sure how much further I'll be able to get with this one on my own - I'll put out a call for help.

**mrdaave** · 10-05-2023, 09:40 AM

Oh haven´t seen your message.
Never mind - I´m not in a hurry to finish this, but I´m really looking forward to implement this in the company

Thank you again for your help so far!

**mrdaave** · 10-05-2023, 09:38 AM

I found two small bugs in the current formula:

1) Sometimes not all the additional information is provided - I think its due to if sometimes a hazard-class has two additional information (like 4,5) and the other one has just one (like just 4).
In this case, not all the information will be next to the room.

2) Sometimes, if a room has a single Yes for one GHS-hazard-symbol the room will be able to handle the chemical, although for example for all the other selected symbols its No -> so it should be not allowed to handle the chemical in this room.

But I will check again and try to verify the problem with a concrete exapmle.

**HansDouwe** · 10-05-2023, 12:16 PM

Here is another solution for the possible rooms (without duplicate remarks)

Please try

=LET(
  f,FILTER(Database!B2:U88,ISNUMBER(XMATCH(Database!A2:A88,B4:J4))),
  t,VSTACK(Database!B1:U1,f),
  u,FILTER(t,BYCOL(t,LAMBDA(c,ISNA(XMATCH("No",c))))),
  r,TAKE(u,1),
  z,TRANSPOSE(FILTER(r,r<>0)),
  IFERROR(MAP(z,LAMBDA(x,LET(q,UNIQUE(INDEX(u,,1+XMATCH(x,r))),TEXTJOIN(" , ",,x,XLOOKUP(FILTER(q,q),Database!X4:X14,Database!Y4:Y14))))),"No rooms available"))

**HansDouwe** · 10-05-2023, 01:02 PM

Here is a solution for the special requirements:

Please enter in Database!V1 "Powder" and in Datebase!W1 "Liquid" and try:

=LET(
f,FILTER(INDEX(Database!V2:W88,,XMATCH(D17,Database!V1:W1)),ISNUMBER(XMATCH(Database!A2:A88,B4:J4))),
MAP(SORT(UNIQUE(TEXTSPLIT(TEXTJOIN(", ",,f),,", "))),LAMBDA(x,x&": "&XLOOKUP(x,'Special Requirements Database'!A2:A99,'Special Requirements Database'!B2:B99))))

**mrdaave** · 10-06-2023, 02:39 AM

Good morning Hans,

thank you for this early help!
I've already implemented the solution for the rooms - its working fine so far!
There is just one small thing: for example if I enter "Ätzend, Haut 7C" and "Organische Peroxide EF" in the first two cells, it shows me "No rooms available" for the first three cells and then Room 6,7 and 9 are fine.
Is it possible to just show rooms which are allowed and only if there is no room available to say "No rooms available"?

I will try the special requirements in the meantime!

Again thank you and best regards,

David

**mrdaave** · 10-06-2023, 02:58 AM

Special Requirements work just amazing - thank you very much!

**AliGW** · 10-06-2023, 02:58 AM

Try this:

=LET(
  f,FILTER(Database!B2:U88,ISNUMBER(XMATCH(Database!A2:A88,B4:J4))),
  t,VSTACK(Database!B1:U1,f),
  u,FILTER(t,BYCOL(t,LAMBDA(c,ISNA(XMATCH("No",c))))),
  r,TAKE(u,1),
  z,TRANSPOSE(FILTER(r,r<>0)),
  m,MAP(z,LAMBDA(x,LET(q,UNIQUE(INDEX(u,,1+XMATCH(x,r))),TEXTJOIN(" , ",,x,XLOOKUP(FILTER(q,q),Database!X4:X14,Database!Y4:Y14))))),
  IFERROR(FILTER(m,ISTEXT(m)),"No Room Available"))

**mrdaave** · 10-06-2023, 03:14 AM

Thank you Ali, I think that this will solve the last problem!
I will play a little bit with it and check if everything is working fine

After that I will mark this thread as solved.

For me your help does not come naturally, so I am very thankful for both of your help

You do an amazing job in this forum!

**AliGW** · 10-06-2023, 03:15 AM

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Thanks for the kind rep comment.

**HansDouwe** · 10-06-2023, 04:39 AM

Thank you very much for the challenging question,
it was very fun and educational to find a solution for it.

.

And thanks for the feedback and rep

. Glad to have helped.

**mrdaave** · 10-06-2023, 05:56 AM

It seems to work fine - I could not find any problems.

I will close the thread now and thanks again - I already recommended this forum many times :D

All the best and until the next challenge!

David

**mrdaave** · 10-06-2023, 08:32 AM

I found another problem - I think I need your help again...

In the last sample-book from Ali: If you choose "Organische Peroxide CD", then the chemical should be possible to be handled in room 2, 3, 4, 6, 7 and 9.
At first room 2, 3 and 4 were not able to handle this chemical with the solution of Hans. But I think that this is due to the "," between the numbers of the additional information.
For the formula its 5.7 and not 5 AND 7.

Sorry to ask again for help

**AliGW** · 10-06-2023, 08:37 AM

To be honest, I'd have looked at completely reorganising the workbook for this. The biggest problem you have with this is the data layout itself. I'm sure Hans will be able to resolve the latest issue, but I can't help but think that there'll be more!

**mrdaave** · 10-06-2023, 08:49 AM

I thought of adding some additional columns for each number, but I think that wont work due to BYCOL...

I dont mind to reorganize the workbook, but its already looking pretty good

**AliGW** · 10-06-2023, 09:16 AM

It is, but it's all unnecessarily complex. A better data layout would simplify everything.

**mrdaave** · 10-06-2023, 09:25 AM

I am open to everything that works!
Looking forward to the ideas from Hans

**mrdaave** · 10-06-2023, 09:34 AM

Maybe if we use a combination of letters and numbers, like A1, A2 and so on instead of just numbers it may work.
We fixed it for the special requirements in a similar way.

**HansDouwe** · 10-06-2023, 11:23 AM

Here are my corrected formulas:

Available rooms:

Please try

=LET(
  f,FILTER(Database!B2:U88,ISNUMBER(XMATCH(Database!A2:A88,B4:J4))),
  t,VSTACK(Database!B1:U1,f),
  u,FILTER(t,BYCOL(t,LAMBDA(c,ISNA(XMATCH("No",c))))),
  r,TAKE(u,1),
  z,TRANSPOSE(FILTER(r,r<>0)),
  IFERROR(MAP(z,LAMBDA(x,LET(q,UNIQUE(INDEX(u,,1+XMATCH(x,r))),TEXTJOIN(" , ",,x,XLOOKUP(FILTER(q,q<>"",""),Database!X4:X14,Database!Y4:Y14,""))))),"No rooms available"))

Special requirements:

Please try:

=LET(
f,FILTER(INDEX(Database!V2:W88,,XMATCH(D17,Database!V1:W1)),ISNUMBER(XMATCH(Database!A2:A88,B4:J4))),
IFERROR(MAP(SORT(UNIQUE(TEXTSPLIT(TEXTJOIN(", ",,f),,", "))),LAMBDA(x,x&": "&XLOOKUP(x,'Special Requirements Database'!A2:A99,'Special Requirements Database'!B2:B99))),"-"))

**mrdaave** · 10-09-2023, 01:19 AM

Good morning Hans,

thank you again for your help!
I've just implemented your formulas to my worksheet - it looks great so far! I really like the IFERROR for the special requirements. I have changed the "-" to "no B-Terms necessary" and it seems to be quite professional already

The only thing missing which would make it perfect is, if we are able to add multiple safety precautions for one room and one safety precaution.
For now if we look for example at room two and try to handle "Organische Peroxide CD" we can do this, but we have two additional safety precautions:

- 5: not allowed to be warmed
- 7: need to be in room 9

As long as my database contain these to numbers as "5,7" excel thinks that this is one number and not two.
So we get room 2 with no additional safety precautions because 5,7 is not existing (although 5 and 7 is in the database)

Is there a way to fix this? Maybe if we us a combination of numbers and letters to differentiate between for example 5 and 7?

Thank you again for your tremendous support,

David

**mrdaave** · 10-09-2023, 01:26 AM

If (" , ",,x,XLOOKUP(FILTER(q,q<>"",""),Database!X4:X14,Database!Y4:Y14,""))
should fix this problem I ran into another problem:
My german Office always changes "," into ";" - I have tried to change it, but than the formula does not work

**mrdaave** · 10-09-2023, 02:59 AM

I have another idea (but was not able to adapt it):

For the B-Terms we use TEXTSPLIT. Maybe we can use the same formula for the special requirements as well.
But I am not sure if we still have to use numbers and letters in this case.
I am trying to understand these formulas but if I read them for too long I get confused 😅

**AliGW** · 10-09-2023, 03:12 AM

My german Office always changes "," into ";"

Always open the workbook provided and copy the formula from there to avoid locale difference issues.

If you would like Hans to dissect a formula for you, just ask.

**AliGW** · 10-09-2023, 03:17 AM

Further: you have a Swiss German (or possibly Swiss French) locale where the separator SHOULD be a semi-colon, not a comma. This is why you should ALWAYS copy from the workbook, and NOT the thread.

**mrdaave** · 10-09-2023, 03:18 AM

Hi Ali,

I opened the workbook Hans provided and just copied the formula.
The problem is also present in his workbook. I just was not sure if this is due to different language.

And it would be great, if Hans or somebody else could check this formula and may find a way to filter these numbers correctly.
As in #31-#33 mentioned I can also change the database if necessary.

**AliGW** · 10-09-2023, 03:23 AM

Which locale do you have? German or French? Or something else?

**mrdaave** · 10-09-2023, 03:24 AM

I have German

**AliGW** · 10-09-2023, 03:25 AM

OK. So define exactly what you mean by 'problem' with the formula - are you getting an error message? If so, which?

**mrdaave** · 10-09-2023, 03:32 AM

I don't get an error-message.
The problem is the followin:
Some rooms have additional requirements (such as "max 10 g" etc.)
I have listed them next to the rooms with numbers but sometimes there are more than one additional requirement. They are listed with a comma (for example: 5,7)
Excel counts this as 5.7 and not 5 and 7 -> this leads to no additional requirements in the "Search-Sheet".
I would need a solution to show all additional requirements listed next to the rooms.

My idea was to add letters next to the numbers (for example A1 to A11) to get rid of the problem - but somehow this doesn't work as well

**AliGW** · 10-09-2023, 03:33 AM

OK - then I suggest you just wait patiently until Hans chimes in.

**mrdaave** · 10-11-2023, 02:21 AM

Good morning Ali,

may you can ask Hans for his considerations?
I don't want to disregard the forum rules but would be happy if somebody could help me to find a solution for the last problem.

Thank you in advance and please excuse the additional request.

Best regards, David

**AliGW** · 10-11-2023, 02:24 AM

No, I can't do that, and nor can you. Whether or not a member chooses to respond is entirely at their own discretion.

If Hans wishes to continue helping here, he will respond. It's above my head, but maybe others might help. What I can do is put out a general call for help.

**mrdaave** · 10-11-2023, 02:27 AM

That would be great - every help is appreciated

Thank you for the support Ali!

**AliGW** · 10-11-2023, 02:30 AM

Have done so - fingers crossed. It's gone beyond my capabilities - sorry.

**mrdaave** · 10-11-2023, 02:40 AM

Thank you very much! You are doing great work in this forum - hopefully somebody has an idea

**Glenn Kennedy** · 10-11-2023, 02:48 AM

Please upload a file showing the problem and showing what you expect to see. It's not practical to go through all the previous posts.

**mrdaave** · 10-11-2023, 03:10 AM

Hi Glen, thanks for joining this thread!

I've added the workbook of Hans and added the expected results.
The problem is in the database:
For the additional information (for example: not allowed to be warmed) I have added numbers in an additional column next to every room.
Sometimes there are more than just one additional information, which leads to more numbers in the column next to every room (for example 5,7)
Excel counts this as 5.7 (one number) and not as 5 AND 7 (two numbers)
In this case 5,7 is not existing in the database and i get no additional information.

My idea was to maybe add letters next to the numbers, so excel can't count them as numbers anymore (to evade the problem with "," beetween numbers) but I was not able to get it working.

Thank you in advance for your time and help!

**AliGW** · 10-11-2023, 03:13 AM

Maybe change the separator in the formula to a semi-colon so that your locale doesn't mistake it for a decimal separator.

BUT your workbook does NOT show the issue!!! Please set it up so that it does.

**AliGW** · 10-11-2023, 03:27 AM

Forget that - I see the issue now.

**HansDouwe** · 10-11-2023, 03:29 AM

I tried to adjust the formula quickly yesterday, but it didn't work.

I need to reset my brain and take more time for that. I will try again this afternoon.

I expect that a solution can be found.
The only restriction is, if you have decimal point is comma then you should not record 2 comments as 5,7, because that will change to a decimal number. I suggest putting a space behind each comma. So 5, 7. Or you should use a different separator.

**AliGW** · 10-11-2023, 03:57 AM

All commas in Database need replacing witha semi-colon. I am just working on something now. Can be done quickly with Find & Replace.

**mrdaave** · 10-11-2023, 03:59 AM

I understand - sorry for this misleading.
I've tried to change the database to for example 5, 7 - but could not find a solution for this as well...

**Glenn Kennedy** · 10-11-2023, 03:59 AM

Try this:

Not fully road-tested. But the separators mUST be semicolons. Hopefully it works OK...

Formula:

=LET(

A,FILTER(Database!B2:U88,ISNUMBER(XMATCH(Database!A2:A88,B4:J4))),

B,VSTACK(Database!B1:U1,A),

C,FILTER(B,BYCOL(B,LAMBDA(z,ISNA(XMATCH("No",z))))),

D,TAKE(C,1),

E,TRANSPOSE(FILTER(D,D<>0)),

F,TAKE(C,-1),

G,TRANSPOSE(FILTER(F,(F<>"Yes")*(F<>0))),

H,IFERROR(DROP(REDUCE("",G,LAMBDA(x,y,VSTACK(x,--TEXTSPLIT(y,";")))),1),""),

I,BYROW(H,LAMBDA(x,TEXTJOIN(", ",,IFERROR(VLOOKUP(x,Database!X4:Y14,2,FALSE),"")))),

BYROW(HSTACK(E,I),LAMBDA(X,TEXTJOIN(", ",,X))))

**Glenn Kennedy** · 10-11-2023, 04:01 AM

It failed at my first check... MULTIPLE curses.

**AliGW** · 10-11-2023, 04:02 AM

@mrdaave

What's the MAXIMUM number of special requirements in RED that can exist in Database for any one room? Is it just TWO, or could there be more?

**Glenn Kennedy** · 10-11-2023, 04:05 AM

OK. I think mine is OK for SINGLE substances. Once you select a second one, it falls over. What do you want to happen if 2 or more substances are selected?

**AliGW** · 10-11-2023, 04:12 AM

I understand - sorry for this misleading.
I've tried to change the database to for example 5, 7 - but could not find a solution for this as well...

Please answer my question in post #60.

For now, you do NOT need to be doing anything. Just wait until we get back to you.

**AliGW** · 10-11-2023, 04:19 AM

Try this in L9 (I've set it up in N9):

=LET(
  f,FILTER(Database!B2:U88,ISNUMBER(XMATCH(Database!A2:A88,B4:J4))),
  t,VSTACK(Database!B1:U1,f),
  u,FILTER(t,BYCOL(t,LAMBDA(c,ISNA(XMATCH("No",c))))),
  r,TAKE(u,1),
  s,DROP(TAKE(u,-1),,1),
  v,VSTACK(r,s),
  z,TRANSPOSE(FILTER(v,r<>0)),
x,REDUCE(,z,LAMBDA(a,b,IFERROR(VSTACK(a,TEXTSPLIT(b,";")),""))),
q,FILTER(x,LEFT(INDEX(x,,1),4)<>"Room"),
h,HSTACK(DROP(FILTER(x,LEFT(INDEX(x,,1),4)="Room"),,-1),FILTER(x,LEFT(INDEX(x,,1),4)<>"Room")),
a,Database!X4:X14,b,Database!Y4:Y14,
sc_1,BYROW(--INDEX(h,,2),LAMBDA(r,XLOOKUP(r,a,b,""))),
sc_2,BYROW(--INDEX(h,,3),LAMBDA(r,XLOOKUP(r,a,b,""))),
MAP(INDEX(h,,1),sc_1,sc_2,LAMBDA(x,y,z,TEXTJOIN(", ",,x,IFERROR(y,""),IFERROR(z,"")))))

It will handle up to TWO special ROOM conditions. I have replaced ALL commas in Database with semi-colons.

This would place it all in one cell with Wrap Text set for the cell (try in B9):

=LET(
  f,FILTER(Database!B2:U88,ISNUMBER(XMATCH(Database!A2:A88,B4:J4))),
  t,VSTACK(Database!B1:U1,f),
  u,FILTER(t,BYCOL(t,LAMBDA(c,ISNA(XMATCH("No",c))))),
  r,TAKE(u,1),
  s,DROP(TAKE(u,-1),,1),
  v,VSTACK(r,s),
  z,TRANSPOSE(FILTER(v,r<>0)),
x,REDUCE(,z,LAMBDA(a,b,IFERROR(VSTACK(a,TEXTSPLIT(b,";")),""))),
q,FILTER(x,LEFT(INDEX(x,,1),4)<>"Room"),
h,HSTACK(DROP(FILTER(x,LEFT(INDEX(x,,1),4)="Room"),,-1),FILTER(x,LEFT(INDEX(x,,1),4)<>"Room")),
a,Database!X4:X14,b,Database!Y4:Y14,
sc_1,BYROW(--INDEX(h,,2),LAMBDA(r,XLOOKUP(r,a,b,""))),
sc_2,BYROW(--INDEX(h,,3),LAMBDA(r,XLOOKUP(r,a,b,""))),
TEXTJOIN(CHAR(10),,MAP(INDEX(h,,1),sc_1,sc_2,LAMBDA(x,y,z,TEXTJOIN(", ",,x,IFERROR(y,""),IFERROR(z,""))))))

**Glenn Kennedy** · 10-11-2023, 04:23 AM

whereas mine will handle any number of specials.. but at the moment only for a single compound type. I still need to know how results for multiple compounds are to be presented.

**AliGW** · 10-11-2023, 04:24 AM

I think you're looking at part #2, Glenn - I am still fixing part #1!!!

**HansDouwe** · 10-11-2023, 04:51 AM

There is no problem with part #2, the special requirements.
The second formula in Post #34 does the job.

**Glenn Kennedy** · 10-11-2023, 05:16 AM

I was looking at Part 1... look at the file!

**AliGW** · 10-11-2023, 05:19 AM

OK - however I thought the substances (compounds) were dealt with in part #2, which is why I thought you were dealing with that. Sorry.

And I've looked and can't see what's wrong with yours for part #1 ...

**mrdaave** · 10-11-2023, 05:38 AM

Please excuse my late answer!

According your question at #60: it can contain more than 2 special requirements. For now its Maximum is 3.
I will have a look at the other posts and check the solution provided from Glen

**AliGW** · 10-11-2023, 05:42 AM

Go with Glenn's suggestion, then.

**mrdaave** · 10-11-2023, 05:49 AM

According #64:

Chemicals can have a combination of a maximum of 5 GHS-hazard classes.
For example:

B4: Organische Peroxide CD -> 5;7
D4: Akut-toxisch7 -> 7
F4: Mutagen 7A -> 9
H4: Ätzend, Haut 7A -> 11
J4: Karzinogen 7A -> 9

In this case I would like to add up all the additional requirements (not including doubles)

For this chemical it would be for room 2 (just as an example): 5;7;9;11

**Glenn Kennedy** · 10-11-2023, 06:16 AM

What is the expected answer for the scenario as set up in THIS file? The answer generated (rightly or wrongly) is in the purple shaded cells.

**mrdaave** · 10-11-2023, 07:50 AM

Right would be:
Additional information 5 and 7 for room 2/3; from 1-9 for room 4; 5 for 6/7; 5-11 for room 9

Wrong would be:
Additional information 5 (twice) and 7 (twice) for room 2/3; from 1-9 (5 and 7 twice) for room 4; 5 (twice) for 6/7; 5-11 (5 twice) for room 9

**Glenn Kennedy** · 10-11-2023, 07:57 AM

Show me EXACTLY what you expect in the file.

**mrdaave** · 10-11-2023, 08:02 AM

Right answer is shown in O1:O7, wrong answer in P1:P7
I can still change the database if that would make things easier.
My attempt was to make a combination with numbers and letters (like A1 - A11) instead of just numbers (1-11). Then it would be similar to the second part with the b-terms. But wasn't able to get it to work...

**Glenn Kennedy** · 10-11-2023, 08:07 AM

OK. I think....

I guess that I'll have to start over again, as I suspect it isn't a minor tweak.

**mrdaave** · 10-11-2023, 08:11 AM

Oh no

I'm sorry for the inconveniences - if I can provide any further information or help, I will do my best to do so!

**Glenn Kennedy** · 10-11-2023, 08:41 AM

You >>>MUST<<< use ; as the separator, not a mix of , and ; BOTH were present in the file you uploaded. I think I've removed them all.

I think this does it...

Formula:

=LET(

A,FILTER(Database!B2:U88,ISNUMBER(XMATCH(Database!A2:A88,B5:J5))),

B,VSTACK(Database!B1:U1,A),

C,FILTER(B,BYCOL(B,LAMBDA(z,ISNA(XMATCH("No",z))))),

D,TAKE(C,1),

E,TRANSPOSE(FILTER(D,D<>0)),

F,DROP(C,1,1),

G,BYCOL(F,LAMBDA(x,TEXTJOIN(";",,x))),

H,TRANSPOSE(FILTER(G,(LEFT(G,1)<>"Y")*(G<>""))),

I,IFERROR(DROP(REDUCE("",H,LAMBDA(x,y,VSTACK(x,--UNIQUE(TEXTSPLIT(y,";"),1)))),1),""),

J,BYROW(I,LAMBDA(x,TEXTJOIN(", ",,IFERROR(VLOOKUP(x,Database!X4:Y14,2,FALSE),"")))),

BYROW(HSTACK(E,J),LAMBDA(X,TEXTJOIN(", ",,X))))

It's all broken up into baby steps to make it easy (I hope) to unpick if needed!!

**HansDouwe** · 10-11-2023, 08:48 AM

After some fiddling, here is a solution for the available rooms with the accompanying comments.

In the database, separate the comment numbers with a ; and try this formula:

=LET(
  f,FILTER(Database!B2:U88,ISNUMBER(XMATCH(Database!A2:A88,B4:J4))),
  t,VSTACK(Database!B1:U1,f),
  u,FILTER(t,BYCOL(t,LAMBDA(c,ISNA(XMATCH("No",c))))),
  r,TAKE(u,1),
  z,TRANSPOSE(FILTER(r,r<>0)),
  IFERROR(
    MAP(z,LAMBDA(x,LET(q,IFERROR(SORT(--UNIQUE(TEXTSPLIT(TEXTJOIN(";",,INDEX(u,,1+XMATCH(x,r))),,";"))),""),x&IF(INDEX(q,1)<>"",", "&TEXTJOIN(", ",,XLOOKUP(q,Database!X4:X14,Database!Y4:Y14,"")),"")))),
    "No rooms available"))

There is virtually no limit to the number of possible comments.

**mrdaave** · 10-11-2023, 08:49 AM

Thank you Glenn!
This may take a bit time to check and implement in my workbook - but it seems to solve the problem at first look

**HansDouwe** · 10-11-2023, 09:16 AM

Did you see post #79?

**HansDouwe** · 10-11-2023, 09:52 AM

I compared the results of Glenn's and my formula.

If there is a room without an associated text, the texts in Glenn's formula will shift and 1 room will not be shown.
This works well in the other formula.

See attachment.

**mrdaave** · 10-11-2023, 10:08 AM

Oh perfect, I was trying hard to understand why something looks strange in my worksheet

I will try to change to your formula Hans and will give you feedback if it works now!
Thank you for your time and effort!

**mrdaave** · 10-11-2023, 11:00 AM

I found one mistake, but my brain cannot find the cause for it today.

Somehow with a special combination of GHS-hazard-symbols I get the text "No rooms available" between rooms that are possible. But just for my worksheet and not yours (as seen in the screenshot)

That's why I have to check the formula again and all the combination of data which is different to the samplesheet
I will have a look again tomorrow morning with fresh eyes.

Anyway a huge thanks for your help so far - Ali, Hans and Glenn, you're amazing

**Glenn Kennedy** · 10-11-2023, 11:12 AM

Check for , in the database.

**HansDouwe** · 10-11-2023, 12:17 PM

Please check the range of B4:J4 (my sheet) vs B5:J5 Glenn's sheet) also.

If you cannot find the cause, replace the confidential comments with meaningless comments and upload your sheet.
Although I have done my best, it cannot be ruled out that something is still wrong in the formula.

**mrdaave** · 10-12-2023, 02:21 AM

Good morning Glenn and Hans,

I found the problem, but it's strange:
My additional texts (1-11) are different from the one in the samplebook, containing confidential data.
They are longer, but just containing words and numbers (no special separators etc.)
If I change the first additional text (No. 1) to just a "x" or use the texts from the samplebook everything works fine.
I was also able to reproduce the problem in the samplebook (this was hard to find) - I think its due to the overload of text in just one cell.
If I type a lot of nonsense for each number (just many different words) it works for non of the rooms - as you can see in the attachment.

I have tried to wrap the text, but no success. I can shorten these texts, but if there is a solution to contain all the data in these texts it would be perfect!

**AliGW** · 10-12-2023, 02:32 AM

Guten Morgen!

The #SPILL! error means that there's not enough room for the spill array. Move the formula below down the page a bit to make room:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	M
2	Room 1, asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
3	Room 2
4	Room 3, asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
5	Room 4
6	Room 5 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
7	Room 6
8	#N/A
9	#N/A
10	#N/A
11	#N/A
12	#N/A
13	#N/A
14	#N/A

Sheet: Search

**mrdaave** · 10-12-2023, 02:40 AM

Good morning Ali,

thanks for reaching out!
This works for the formula from Glenn - but there is #N/A for every room where the product is not able to be handled and also the shift from one room to another (post #82) so I will get a wrong information for some rooms.
With the formula from Hans this problem is solved, but I am limited with the characters. In your attachment there are no rooms available for the formula from Hans.

**AliGW** · 10-12-2023, 02:43 AM

This will resolve that:

=LET(
A,FILTER(Database!B2:U88,ISNUMBER(XMATCH(Database!A2:A88,B5:J5))),
B,VSTACK(Database!B1:U1,A),
C,FILTER(B,BYCOL(B,LAMBDA(z,ISNA(XMATCH("No",z))))),
D,TAKE(C,1),
E,TRANSPOSE(FILTER(D,D<>0)),
F,DROP(C,1,1),
G,BYCOL(F,LAMBDA(x,TEXTJOIN(";",,x))),
H,TRANSPOSE(FILTER(G,(LEFT(G,1)<>"Y")*(G<>""))),
I,IFERROR(DROP(REDUCE("",H,LAMBDA(x,y,VSTACK(x,--UNIQUE(TEXTSPLIT(y,";"),1)))),1),""),
J,BYROW(I,LAMBDA(x,TEXTJOIN(", ",,IFERROR(VLOOKUP(x,Database!X4:Y14,2,FALSE),"")))),
BYROW(HSTACK(E,J),LAMBDA(X,IFERROR(TEXTJOIN(", ",,X),""))))

I have not yet looked at Hans' formula.

**mrdaave** · 10-12-2023, 03:03 AM

Originally Posted by AliGW

This will resolve that:

(#90)

This resolves the problem with #N/V, but I still get the wrong information if some rooms don't have an additional text

For #94: I'll try to change the conf. data to something different and then send the original workbook

**Glenn Kennedy** · 10-12-2023, 02:46 AM

Are you now all sorted out?

**AliGW** · 10-12-2023, 02:46 AM

If you remove the IFERROR element from qq in Hans' solution, you get this:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	M
21	Room 1, asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
22	Room 2, asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
23	Room 3, asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
24	Room 4, asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
25	Room 5 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
26	Room 6, asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23 , asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23

Sheet: Search

=LET(
  f,FILTER(Database!B2:U88,ISNUMBER(XMATCH(Database!A2:A88,B5:J5))),
  t,VSTACK(Database!B1:U1,f),
  u,FILTER(t,BYCOL(t,LAMBDA(c,ISNA(XMATCH("No",c))))),
  r,TAKE(u,1),
  z,TRANSPOSE(FILTER(r,r<>0)),
  qq,
    MAP(z,LAMBDA(x,LET(q,IFERROR(SORT(--UNIQUE(TEXTSPLIT(TEXTJOIN(";",,INDEX(u,,1+XMATCH(x,r))),,";"))),""),x&IF(INDEX(q,1)<>"",", "&TEXTJOIN(", ",,XLOOKUP(q,Database!X4:X15,Database!Y4:Y15,"")),"")))),qq)

**mrdaave** · 10-12-2023, 03:01 AM

I tried to fix it, but in my case I get #VALUE for rooms which have been "no room available"
Although if I shorten the additional texts it shows me rooms including the texts. Textwrap doesnt help either

I have the same height for each row (changed manually). Does this trigger this problem with #VALUE?

**AliGW** · 10-12-2023, 03:03 AM

No, and you need to attach a workbook showing the problem that you are encountering (probably user error).

**AliGW** · 10-12-2023, 03:05 AM

That's too vague a response to be helpful, sorry. Be SPECIFIC: "x should be y, not z because ..."

**mrdaave** · 10-12-2023, 03:16 AM

Workbook without conf. data is in the attachments.

For the prior solution Ali provided with the formula from Glenn:
The formula doesn't use the correct numbers in some cases. If there is one room with no additional information it shifts to the next one (I don't really get everything in this formula, but that's what I understood with the help from Hans).
I used both formulas (Glenn and Hans) and got the same rooms but different additional information.
I hope this makes the problem a bit more clear.

**HansDouwe** · 10-12-2023, 03:18 AM

The error occurred when the length of the comments exceeded 255 characters.
That's weird, because limit is 32767 characters.
When I removed the IFERROR option from the formula, the formula suddenly went well.
That is also strange, because then it seems that despite there was no ERROR the formula was still executed the IFERROR part of the formula.

The IFERROR section was intended for the situation with no available rooms.
I have now solved this in a different way.

Hereby the new working formula that also working correct with long comments:

=LET(
  f,FILTER(Database!B2:U88,ISNUMBER(XMATCH(Database!A2:A88,B5:J5))),
  t,VSTACK(Database!B1:U1,f),
  u,FILTER(t,BYCOL(t,LAMBDA(c,ISNA(XMATCH("No",c))))),
  r,TAKE(u,1),
  z,TRANSPOSE(FILTER(r,r<>0,"")),
  IF(z="",
    "No rooms available",
    MAP(z,LAMBDA(x,LET(q,IFERROR(SORT(--UNIQUE(TEXTSPLIT(TEXTJOIN(";",,INDEX(u,,1+XMATCH(x,r))),,";"))),""),x&IF(INDEX(q,1)<>"",", "&TEXTJOIN(", ",,XLOOKUP(q,Database!X4:X15,Database!Y4:Y15,"")),""))))))

**AliGW** · 10-12-2023, 03:19 AM

But you haven't bothered to annotate it to show what is right and what is wrong! Your error reports are too generic - you need to get down to a granular level: which rooms have the wrong data? What should that data be? Be more SPECIFIC, please, otherwise if will be another 97 posts before we reach a conclusion.

**AliGW** · 10-12-2023, 03:21 AM

If you remove the IFERROR from Hans' formula (déjà vu, anyone?), you get this (tell us what is right/wrong about it and WHY):

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	D
4	9-238 Fl., Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam corpora. Kasd pretium cetero qui, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam corpora. Kasd pretium cetero qui, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam
5	9-207 Fl., Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam corpora. Kasd pretium cetero qui, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam corpora. Kasd pretium cetero qui, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam
6	9-209 Fl., Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam corpora. Kasd pretium cetero qui, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam corpora. Kasd pretium cetero qui, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide.
7	9-206, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam corpora. Kasd pretium cetero qui, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam corpora. Kasd pretium cetero qui, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide.
8	9-243, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam corpora. Kasd pretium cetero qui, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide., Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam, Lorem ipsum semper habeo duo, ut vis. Aliquyam eu splendide. Ut mei eteu nec antiopam

Sheet: Mögliche Räume

**HansDouwe** · 10-12-2023, 03:29 AM

It was clear for me what was wrong and what the expected results should be.

See solution in post #98.

**Glenn Kennedy** · 10-12-2023, 03:30 AM

This is getting confusing:

a) because you, Mrdave, are not being explicit about exactly where the problem is.

b) the attachment at Post 97 does not seem to contain my formula.. at least I can't see it

c) there are 3 of us (plus you) playing with 2 sets of formulae.

This is a recipe for endless confusion. I'll step aside at this point. I'll look back later to see how things have progressed.

**mrdaave** · 10-12-2023, 03:32 AM

This is a comment to the solution Hans provided in his last post:

If I choose the following GHS-hazard-classes:

Organische Peroxide CD, Brennbare Flüssigkeiten 2, Akut-toxisch7

following problem occurs: #VALUE

The right answer would be:
Room 1, Text for 1,5,7,8
Room 2, Text for 1,5,7,8
Room 3, Text for 1,5,7,8
Room 4, Text for 1,2,4,5,8,9
Room 5, Text for 1,5,8,9
Room 6, Text for 1,2,5,8,9

I will comment #97 as well, but it takes some time to compare this two formulas and define the exact problem

I think this is due to the max characters in one cell.

**AliGW** · 10-12-2023, 03:32 AM

I shall step aside, too, at least until the OP has reviewed Hans' latest (post #98) suggestion.

I think this is due to the max characters in one cell.

So what happened when you reduced the characters in that cell??? Did you test your theory?

**mrdaave** · 10-12-2023, 03:35 AM

Originally Posted by AliGW

So what happened when you reduced the characters in that cell??? Did you test your theory?

Yes! If i use for example just one letter/number/etc. everything works.

**AliGW** · 10-12-2023, 03:41 AM

So there's your solution, then. Your text needs to be 255 characters or fewer.

Anhyway, over to Hans.

**HansDouwe** · 10-12-2023, 04:00 AM

Your text needs to be 255 characters or fewer.

That is not necessary with the formula of Post 98.

**mrdaave** · 10-12-2023, 04:10 AM

Originally Posted by HansDouwe

That is not necessary with the formula of Post 98.

Hans, can you have a look at the attachment at post #103?
Still got the #VALUE ERROR

**mrdaave** · 10-12-2023, 04:08 AM

Additional to #97 to make it clear:

Solution Glenn:
Room 1: Text for 1,5,7,8
Room 2: nothing
Room 3: Text for 1,5,7,8
Room 4: nothing
Room 5: Text for 1,5,7,8,9
Room 6: nothing

This solution is wrong, because the additional text should be provided as in Hans' solution:

Room 1: Text for 1,5,7,8
Room 2: Text for 1,5,7,8
Room 3: : Text for 1,5,7,8,9
Room 4: Text for 1,4,5,7,8
Room 5: Text for 1,5,8,9
Room 6: Text for 1,2,5,8,9

The problem with Hans formula is the limitation to 255 characters or fewer.
As in post #106 this would be a solution. But we are still limited with the 255 characters or fewer.
Is there a possibility or workaraound to exceed this limitation?

If not I will try to shorten them to not exceed the 255 characters.

Furthermore #98 (latest solution provided from Hans) is commented at #103

**HansDouwe** · 10-12-2023, 04:23 AM

The problem with Hans formula is the limitation to 255 characters or fewer.

No the formula does not have a limitation to 255 charachters. See example with room 3 and 4 with over 300 characters.

**AliGW** · 10-12-2023, 04:24 AM

One small observation: Hans' formula refers to "No", but the database table is in German ("Nein").

Also, it would help if you added a number to your conditions so that we can easier see what is being pulled through:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	X	Y
4	1	1 asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
5	2	2 asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
6	3	3 asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
7	4	4 asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
8	5	5 asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
9	6	6 asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
10	7	7 asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
11	8	8 asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
12	9	9 asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
13	10	10 asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
14	11	11 asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23
15	12	12 asdf ölkjas asödlfkj asdföjw aölkdj ölkas ölka ölkjasd ö2 ö9 d90 23

Sheet: Database

**mrdaave** · 10-12-2023, 04:33 AM

Please excuse this bummer from me - tried to reproduce the problem from the original worksheet and messed it up a bit...

Changed the Nein to No and Ja to Yes

Now there is the #VALUE again as you can see in the reworked attachment from Hans

Edit: Added numbers to the additional comments to make it clear as Ali suggested.

**HansDouwe** · 10-12-2023, 04:54 AM

Also, the formula of Post 98 works well in the file of Post 103. See attachment.

Have all the problems now been solved?

**HansDouwe** · 10-12-2023, 05:00 AM

The formula works well in the file of Post 112 also. See attachement.

**mrdaave** · 10-12-2023, 05:54 AM

That is strange - if I open your workbook it works, but as soon as I change one single GHS-hazard-symbol it changes to this:

**AliGW** · 10-12-2023, 06:05 AM

Tell us EXACTLY what and how you are changing things so that we can replicate it. Which version of the workbook are you using?

**mrdaave** · 10-12-2023, 07:18 AM

Originally Posted by AliGW

Tell us EXACTLY what and how you are changing things so that we can replicate it. Which version of the workbook are you using?

Sampleworkbook Hans #114
Change "akut-toxisch7" (or any other GHS-symbol) to anything and back to "akut-toxisch7"
Then the problem with #VALUE occur

@Hans - I will check for Excelversion and the TEXTJOIN. brb

**HansDouwe** · 10-12-2023, 06:56 AM

if I open your workbook it works, but as soon as I change one single GHS-hazard-symbol it changes to this:

Oh, the exact opposite happens to me.
When I open your example I see #VALUE, but when I change the input parameters the correct answer appears.

When you open an Excel file, the values are loaded, but the formulas are not yet executed.
This only happens after a change to one of the fields that the formula uses.

It seems that your EXCEL does not work exactly the same way as my Excel.
Which version of 365 do you have exactly? And what are you running on?

What does your Excel return with =TEXTJOIN(",",,SEQUENCE(50)) and =TEXTJOIN(",",,SEQUENCE(1000)) ?

**mrdaave** · 10-12-2023, 07:24 AM

Excelversion Microsoft® Excel® für Microsoft 365 MSO (Version 2208 Build 16.0.15601.20644) 64 Bit

For TEXTJOIN:

sequence 50: I get numbers from 1-50 in one cell
sequence 1000: I get numbers from 1-1000 in one cell (but I can only see until 283, rest is hidden)

Edit:
sequence 1000 is the largest possible (for adding zeros at the end (10,100,1000,...)
sequence 1000-1'000'000 I get #CALK
sequence above 1'000'000 I get #VALUE

**AliGW** · 10-12-2023, 07:24 AM

No - not seeing the #VALUE! error at all here. I think this has to be locale-related, as you are both using Eurpoean locales.

2208 is over a year old - why not try running updates from within Excel? Is it Windows or Mac? I'm guessing Windows.

**mrdaave** · 10-12-2023, 07:30 AM

That's interesting.

I can't do any updates due to registration of the admin (PC for work).
I'm using Windows.

**AliGW** · 10-12-2023, 07:31 AM

Ah, OK. Well, it may be significant. Can you ask Admin to update you? Or is that not allowed? Seems odd that they are still on a 2022 release.

**mrdaave** · 10-12-2023, 07:48 AM

I'll ask them to update.
Otherwise I will have to wait, until they regularly update office.
I wasn't aware, that this might have an impact

**AliGW** · 10-12-2023, 07:51 AM

More than ever with 365 - MS are rolling out new functionality with every release.

**HansDouwe** · 10-12-2023, 08:25 AM

not seeing the #VALUE! error at all here.

@AlI, I don't think you understand me correctly.

Also, whenever I execute a formula, I never see #VALUE.
I only see the #VALUE when I open a file from the OP with #VALUE.

This is because there is #VALUE in the file and as long as I don't change anything (or enter the formula again) the formula is not executed on my computer and #VALUE remains.
This will be the case on all computers.

As soon as I execute the formula on my computer, it always returns good values.

**HansDouwe** · 10-12-2023, 08:41 AM

I also suspect that the cause of the formula not working correctly is the use of an old version of Excel 365.

**AliGW** · 10-12-2023, 08:53 AM

@AlI, I don't think you understand me correctly.

Also, whenever I execute a formula, I never see #VALUE.
I only see the #VALUE when I open a file from the OP with #VALUE.

I understand you perfectly. My point is that I do not see the error at all when I open the file from the OP or from you or when I change any details. But I do agree that this may well have to do with the OPs older version of 365.

**Glenn Kennedy** · 10-12-2023, 10:29 AM

I see the #VALUE here, too. Until I force recalculation. Then it's fine.

If you're not too battle-weary yet, mrdave... can you tell me what is wrong with my formula and I'll have another look.

**mrdaave** · 10-12-2023, 11:05 AM

An IT-Ticket should hopefully resolve the problem with the Office-Version. But this can take some days

@Glenn: As long as all of you sacrifice your valuable time and support me, I can't get battle-weary! For me this support doesn't come naturally and I will provide data and help wherever I can.

To explain the formula:
There are two issues (one minor, one major):

Minor issue/blemish: I get #NV for some rooms that are not able to handle the chemical with the entered GHS-hazard-classes. If possible I would like to show just the rooms which are able to handle the according chemical. Unfortunately I can't tell you why this occurs. In total we have 10 rooms in the database and X(couldn't find the exact number)-13 rows shown (some with #NV) in the formula.

Major issue: Sometimes there is a different/wrong result compared to Hans'. When you for example choose "Organische Peroxide B" and "Brennbare Flüssigkeiten 1" room 6 should contain the texts for 1,4 and 5. Your formula just contains 4 and 5. The reason for this exceeds my Excel-knowledge as well

I will pause working for today and would come back here early tomorrow. If there is something unclear with my explanation I'll try to show it better in a samplebook. Thanks again for the help so far!

**HansDouwe** · 10-12-2023, 11:27 AM

@Glenn, see my sheet in Post#98 for both issues. Your formula and my formula is in that sheet, so you can see the differences.

**mrdaave** · 10-16-2023, 02:20 AM

Good morning everybody,

I've got the update for Excel and am using Version 2302 now.
I tried the samplebook from Hans and Glenn in post #114
I can now see the whole text in Glenns formula, with Hans' formula I still get #VALUE.
Is there a way to either fix the problem in Glenns formula (see post #129) or Hans' formula (see post #115)

Hope you had a great weekend and thank you in advance!

**AliGW** · 10-16-2023, 02:22 AM

Which workbook should we be looking at? It would help if you attached the one you are having issues with.

**mrdaave** · 10-16-2023, 02:36 AM

There is an issue with all workbooks that contain either Hans' or Glenns formula, but I have looked at the one in #114 (as in attachment now)

To show the problem with Glenns formula:
just open workbook and compare room 6 with Glenns and Hans' formula -> there are wrong numbers next to it for Glenns formula (cannot explain why)

To show the problem with Hans' formula:
Change Database Y4:Y15 with Z4:Z15 - I get #VALUE for some rooms exceeding 255 characters in some cells (this is no problem with Glenns formula)

**AliGW** · 10-16-2023, 02:50 AM

I changed Y4:Y15 to Z4:Z15 - NO error:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	M
17	Room 1, 1 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23 , 5 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23 , 7 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23
18	Room 2, 1 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23 , 5 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23 , 7 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23
19	Room 3, 1 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23 , 5 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23 , 7 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23
20	Room 6, 1 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23 , 5 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23
21
22

Sheet: Search

**AliGW** · 10-16-2023, 03:04 AM

One with over 500 charcters still works:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	M	O
17	Room 1, 5 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23 , 7 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23	154
18	Room 2, 5 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23 , 7 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23	154
19	Room 3, 5 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23 , 7 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23	154
20	Room 4, 4 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23 , 5 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23	154
21	Room 5 , 5 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23	81
22	Room 6, 5 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23	80
23	Room 7, 5 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23	80
24	Room 9, 5 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23 , 6 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23 , 7 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23 , 8 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23 , 9 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23 , 10 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23 , 11 asdf ölkYess asödlfkj asdföjw aölkdj ölkas ölka ölkYessd ö2 ö9 d90 23	526

Sheet: Search

**mrdaave** · 10-16-2023, 03:55 AM

Is this for Hans' formula? Because I still get #VALUE
May this still be due to another version?
Attachment is the same workbook from #133

**HansDouwe** · 10-16-2023, 06:22 AM

I use version 2309 of Excel 365 and there are no problems in my formula with (very) long descriptions here.
Perhaps Microsoft fixed a bug between versions 2302 and 2309?

**mrdaave** · 10-16-2023, 07:16 AM

Hi Hans,

I opened your workbook and as long as I don't change anything it looks good.
As soon as I enter something different the #VALUE Error occurs.

But all of you spent so much time with this problem/thread, I think I will just stick to your formula Hans and hope that this bug will be fixed when we change to Version 2309. In the meantime I will try to shorten the according texts.
In case the bug isn't fix in the update I would reopen the thread or make a new one (just if we REALLY need the whole texts).

But thank you for your time and effort - I didn't get disappointed a single time in this forum!

**HansDouwe** · 10-16-2023, 07:48 AM

If you only open the Excel file the formulas are not executed on your computer.

Thanks for the feedback and rep

.

**AliGW** · 10-16-2023, 10:07 AM

It was Hans’ formula.

**mrdaave** · 10-17-2023, 01:23 AM

Originally Posted by AliGW

It was Hans’ formula.

Sorry Ali, I don't get it - what do you mean with "It was Hans' formula?"

**AliGW** · 10-17-2023, 01:25 AM

Post #136 (to me):

Is this for Hans' formula? Because I still get #VALUE

Post #140 (to you):

It was Hans’ formula.

You:

I would reopen the thread or make a new one

You would need to reopen this one and continue here - duplicate threads are not allowed.

**mrdaave** · 10-17-2023, 01:35 AM

Okay - now I get it 😅
Thank you again for the help!
And I would reopen the thread if necessary.

**AliGW** · 10-17-2023, 01:36 AM

Here - you'd just remove the SOLVED tag and add a new post to the thread, which would bump it to the top of the forum.

**mrdaave** · 10-17-2023, 02:04 AM

Perfect, thank you very much Ali!

Show working-instructions depending on GHS-hazard-pictograms

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms

Re: Show working-instructions depending on GHS-hazard-pictograms