Pull/pick those numbers of column Y that are dependent of the same value (found in column X) etc.

[communist]

Hi,

I'll try to explain it with the help of the table at the end of this message.
I want to "pull" only these values of column Y (for a value comparison in a different table) that are dependent on the same value in column X. In the table, the first four values in column Y have got the same X (highlighted in red). Same is valid for blue- green- and purple-colored row(s).
How can I achieve my Goal without using VBA? Please help. Would aprreciate that.
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[communist]

Real-world example:
Y Shows the days of a certain month, Kind of target days for delivery. X Shows articles that are delivered on a certain day during the month; delivery day is shown in a different table. Now I want to run a Loop that pulls the target days of the same article towards a match-function (that compares target with true delivery day).
I want to run this comparison for every article therefore I Need Kind of a Loop.

I don't have any idea for the build-around, i.e. how to tell a Loop to return the relevant cells to the match-function, e.g. for x=100, it should return 1,2,3,4 to the match-function and look for a match with the value in the second table.
After that it should return 3, due to X=200 and proceed. Return 5, due to X=300 and proceed. Return 1,2,3, due to X=400 and proceed or stop.

 

[communist]

I'll try it one more time ^^

Table 1
[TABLE="width: 100"]
<tbody>[TR]
[TD]product[/TD]
[TD]Vendor[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]V1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]V2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]V2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]V3[/TD]
[/TR]
</tbody>[/TABLE]

Table 2
[TABLE="width: 100"]
<tbody>[TR]
[TD]product[/TD]
[TD]Target delivery day[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Table 3
[TABLE="width: 100"]
<tbody>[TR]
[TD]Vendor[/TD]
[TD]Possible delivery day[/TD]
[/TR]
[TR]
[TD]V1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]V2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]V3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]V3[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


Goal:
0) Go to table 1, pull a product-vendor-pair, e.g. A/V1.
1a) go got Table 2, look up the target delivery day(s). For product A: 1
1b) go to Table 3, look up the possible delivery day(s). For vendor V1: 1
2) via match-function, any target delivery of product A day equals any possible delivery day of vendor V1? If yes, then TRUE, ok. If not any single target matches any single possible delivery day, return FALSE. Here: TRUE
3) repeat for every product-vendor pair

My issue:
- Vlookup is not possible. A product/vendor can have several "target delivery days"/ "possible delivery days".
-tried it in access but I need a logic that shows all the product-vendor pairs that do not have any TRUE value in step 2. The logic "target" <> "possible" does not lead to results I need, e.g. B/V2 with "target delivery day"=1 would be shown up in despite of having a positive result given that V2 can deliver on one of the two target delivery days.

 

[Eric W]

Welcome to the Board!

I'm glad you restated your question. From your original post, I got a completely different idea of what you wanted. Try this:

Excel 2012

	A	B	C	D	E
Table 1
Product Vendor
A	V1
A	V2
B	V2
C	V3
Table 2
Product	Target Delivery Date
A
B
B
C
Table 3
Vendor	Possible Delivery Date
V1
V2
V3
V3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E11[/TH]
[TD="align: left"]{=OR(IF(A11=$A$3:$A$6,COUNTIFS($A$19:$A$22,$B$3:$B$6,$B$19:$B$22,B11)>0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]