Apr 21 2022 02:08 AM
First of hello Community!
I am using :
Microsoft® Excel® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20272) 64-bit
I need VLOOKUP and IF formula with more conditions, maybe even some other formula:
I have two tables:
First table is with list of data of numbers.
Second Table where these numbers are grouped by same characteristics.
I need some kind of IF and VLOOKUP (or something else) to:
1. If only one ID with characteristic is in first table don't return data.
2. If only one ID with characteristic is repeating don't return data.
3. if there are two or more ID with same Characteristic, then return Characteristic in first table behind each ID.
list of data | Characteristic per data | |||||
ID | Characteristic | ID | Characteristic | |||
1 | what formula to enter? | 1 | x | |||
2 | 8 | x | ||||
3 | 2 | y | ||||
4 | 5 | y | ||||
5 | 7 | y | ||||
1 | ||||||
5 | ||||||
6 | ||||||
7 | ||||||
8 |
thanks in advance!
Apr 21 2022 02:58 AM
Apr 21 2022 03:22 AM
@Jan Karel Pieterse yes, they are not the same value but unique: "1" has its specifications but is under same characteristic "X" and "8" has its specifications different from "8", but is under same characteristic "x" as 1.
Apr 21 2022 05:55 AM
Apr 21 2022 06:43 AM
@Jan Karel Pieterse please see my new table where you can better understand logic and how it should work:
Case tables are what are possibilities that i can have in my sheets, and to the right is fixed table from which there can be readings.
case 1 | Table | |||||
Description | Group | Description | Group | |||
Apple | 0 | Apple | Fruit | |||
Shoe | 0 | Pair | Fruit | |||
Phone | 0 | Potato | Vegetable | |||
Airplane | 0 | Cucamber | Vegetable | |||
Chainsaw | 0 | Carrot | Vegetable | |||
Pencil | 0 | |||||
case 2 | ||||||
Description | Group | |||||
Apple | 0 | |||||
Shoe | 0 | |||||
Phone | 0 | |||||
Airplane | 0 | |||||
Chainsaw | 0 | |||||
Apple | 0 | |||||
case 3 | ||||||
Description | Group | |||||
Apple | Fruit | |||||
Shoe | 0 | |||||
Phone | 0 | |||||
Pair | Fruit | |||||
Airplane | 0 | |||||
Chainsaw | 0 | |||||
Pencil | 0 | |||||
case 4 | ||||||
Description | Group | |||||
Apple | Fruit | |||||
Shoe | 0 | |||||
Phone | 0 | |||||
Pair | Fruit | |||||
Airplane | 0 | |||||
Chainsaw | 0 | |||||
Pencil | 0 | |||||
Apple | Fruit | |||||
case 5 | ||||||
Description | Group | |||||
Apple | Fruit | |||||
Shoe | 0 | |||||
Phone | 0 | |||||
Pair | Fruit | |||||
Airplane | 0 | |||||
Chainsaw | 0 | |||||
Pencil | 0 | |||||
Potato | Vegetable | |||||
Pencil | 0 | |||||
Carrot | Vegetable | |||||
Phone | 0 | |||||
Cucamber | Vegetable | |||||
case 1 - if there is Apple from table but not Pair in table, return 0.
case 2 - if there is Apple repeating but not Pair in table, return 0.
case 3 - in table is Apple and Pair, table should return Fruit
case 4 - in table is Apple repeating but also in table is Pair, table should return Fruit
case 5 - in table is Apple repeating but also in table is Pair, table should return Fruit, but in Table is also other group with same rules, and it returns for Potato, Carrot and Cucumber - Vegetable
Apr 22 2022 07:15 AM
The formula in the attached file seems to do what you are looking for. Actually it's two times the same formula connected by the " & " operator. The part of the formula before " & " checks for "Fruit" and the other checks for "Vegetable".
Apr 23 2022 09:57 AM
Apr 23 2022 11:30 AM
You are welcome. If you have "Fruit 1", "Fruit 2" ..... "Fruit 700" instead of only "Fruit" and "Vegetable" you can apply the formula in the attached file unless you want to see all results at the same time. In the attached file in cells G1 and H1 you can dynamically enter 2 characteristic to return their results.
You can as well combine a formula with e.g. ten times " & " in order to dynamically return 10 results at the same time. The maximum number of characters in a cell in Excel is 32767. Therefore it shouldn't be possible to combine more than roughly 70 fruits in a formula.
Apr 26 2022 01:35 AM
Apr 26 2022 03:25 AM
=IF(AND(COUNTA(UNIQUE(FILTER($D$3:$D$11,$E$3:$E$11=G1)))>1,
COUNTA(UNIQUE(MMULT(--($A$3:$A$12=TRANSPOSE(UNIQUE(FILTER($D$3:$D$11,$E$3:$E$11=G1)))),
SEQUENCE(COUNTA(UNIQUE(FILTER($D$3:$D$11,$E$3:$E$11=G1))),1,1,1))))=
COUNTA(UNIQUE(FILTER($D$3:$D$11,$E$3:$E$11=G1)))+1),
IF(MMULT(--($A$3:$A$12=TRANSPOSE(UNIQUE(FILTER($D$3:$D$11,$E$3:$E$11=G1)))),
SEQUENCE(COUNTA(UNIQUE(FILTER($D$3:$D$11,$E$3:$E$11=G1))),1,1,0))>0,G1,"")
,"")
This is the translation of the suggested formula.
I'd prefer to attach the Excel file which is no problem with Excel 2013 files. However the above formula is from Excel online (i don't work with Office365 or 2021) and i haven't found a way to attach the online file to my reply.
Apr 26 2022 03:41 AM
You can download the workbook from Excel Online to your computer, even if you don't have the latest version of Excel. You can then upload it.
Or you can give other users read-only access to your online workbook.
Apr 26 2022 05:28 AM
Does it work when you open the attached file? I get many error messages maybe because of my older version of Excel.
Apr 26 2022 05:44 AM
It works, thank you
Just in case, you may install Advanced Formula Environment add-in for browser version as well.
Apr 26 2022 05:47 AM
Apr 26 2022 07:10 AM
The formula in the attached file is adapted to ranges $A$3:$A$1000, $D$3:$D$1000 and $E$3:$E$1000. You can enter additional data into these ranges and the formula spills the results in range $B$3:$B$1000. This means that you don't have to copy the formula down. You can as well adapt the ranges to e.g. $A$3:$A$20000, $D$3:$D$20000 and $E$3:$E$20000.
In cells G1, H1 and I1 you can dynamically enter the characteristics from column E for which you want to see the results.
Apr 26 2022 08:55 AM
Sorry, I only confirmed to @Quadruple_Pawn what attached file is opened correctly. Didn't dig the content.
Apr 27 2022 12:03 AM
This is working, but still not 100% to what i need:
So, I receive list of ID. Then i need to copy ID into Mappe 33 for check up, but formula returns only 3 values, that are set in G1, H1 and I1. How do I overcome this "limit" (G1, H1 and I1) if i have 338+ groups ("Characteristic") ?
Apr 27 2022 05:35 AM
The maximum number of characters (digits) in a cell in Excel is 32767. One formula has already about 450 characters (digits). Therefore it shouldn't be possible to combine more than roughly 70 formulas with " & " into one comprehensive formula.
Apr 27 2022 07:35 AM
Thank you for your feedback and the reference to the Advanced Formula Environment add-in.
Apr 28 2022 01:00 AM
thank you very much for you time and instructions.
This will be a great improvement for me.