Retrieve the intersection of multiple cells with respect to columns and rows

H

harzer

Board Regular
Joined
Dec 15, 2021
Messages
153
Office Version
  1. 2016
Platform
  1. Windows
Hello everyone,
My level in vba does not allow me to find a solution to my problem, hence my request to the experts among you.
My file consists of two sheets, the "Crossing" sheet and the "New couples" sheet

Some information:
In the "L" column of the "New couples" sheet, from "L3", we find the list of Males.
In the "F" column of the "Crossing" sheet, from "F2", we find a 2nd list of the same Males.

In line number 2, of the "New couples" sheet, from cell "M2", we find the list of Females.
In the "B" column of the "Crossing" sheet, from "B2", we find a 2nd list of the same Females.

The Range ("M3:AD23") of the "New Couples" sheet is variable in row and column, its size must be determined at the beginning of the code.
This Range that I colored in green is ("M3:AD23") contains the inbreeding rates of all birds, each male in column "L" compared to each female in row number 2.

What I want to do is search for the inbreeding rate of certain birds, these birds are in the "Crossing" sheet:
females are in column "B", males are in column "F".
1. To find each female in column "B" of the "Crossing" Sheet, we will look for it in row number 2, of the "New Couples" sheet.
2. To find each male in column "F" of the "Crossing" Sheet, we will look for it in column "L", of the "New Couples" sheet.
3. The inbreeding rate is found at the intersection of each male with each female, this inbreeding rate will be copied (or Transpose) into column "I" of the "Crossing" sheet.

Let's take an example for the female and the male of the 2nd line of the "crossing" sheet:
The male is: "AET27-035/2022 M", it is in cell "L5" of the "New couples" sheet, the female is: "NIM96-010/2021 F", it is in cell "M2" of the "New couples" sheet, the inbreeding rate is equal to (2.29%), it is at the intersection of these two cells "L5" and "M2", it will therefore be copied to column "I" in the same line is the male "AET27-035/2022 M" and the female "NIM96-010/2021 F".
I remain at your disposal if necessary.
I would be very grateful to anyone who can provide a solution.
Please suggest me a solution with a code in vba because I would need to adapt it for other sheets.
Greetings.


"Crossing" sheet) :

Copier Intersection COL et Lignes .xlsm
ABCDEFGHI
1CagesFemellesPèreMèreMâlesPèreMère
2Cage 1NIM96-010/2021 FNIM96-002/2020 MNIM96-020/2020 F1AET27-035/2022 MNIM96-005/2021 MAET27-068/2021 F2,29%
3NIM96-010/2021 FNIM96-020/2023 MNIM96-011/2022 MNIM96-020/2021 F
4NIM96-010/2021 FNIM96-008/2021 MNIM96-002/2020 MNIM96-020/2020 F
5Cage 2AET27-026/2022 FNIM96-046/2019 MNIM96-036/2021 F25919-001/2023 M3024-058/2020 M3024-011/2022 F
6AET27-026/2022 FAET27-022/2023 MAET27-022/2022 MNIM96-010/2021 F
7AET27-026/2022 FAET27-036/2023 MNIM96-024/2021 MAET27-011/2022 F
8Cage 3AET27-037/2022 FNIM96-005/2021 MAET27-068/2021 F3AET27-005/2024 MAET27-022/2023 MAET27-015/2023 F
9AET27-037/2022 FAET27-006/2024 MAET27-022/2023 MAET27-015/2023 F
10AET27-037/2022 FAET27-067/2024 M5919-001/2023 MAET27-009/2023 F
11Cage 4AET27-038/2022 FNIM96-005/2021 MAET27-068/2021 F4AET27-043/2024 MNIM96-020/2023 MAET27-020/2022 F
12AET27-038/2022 FAET27-026/2024 MAET27-010/2021 MAET27-019/2023 F
13AET27-038/2022 FAET27-005/2024 MAET27-022/2023 MAET27-015/2023 F
14Cage 5AET27-017/2023 FNIM96-008/2021 MAET27-037/2022 F5AET27-017/2024 MNIM96-020/2023 MAET27-038/2022 F
15AET27-017/2023 FAET27-022/2022 MAET27-049/2021 MAET27-003/2021 F
16AET27-017/2023 FAET27-042/2024 MNIM96-020/2023 MAET27-020/2022 F
17Cage 6AET27-023/2023 FAET27-022/2022 MNIM96-010/2021 F6AET27-013/2024 MAET27-035/2022 MAET27-055/2023 F
18AET27-023/2023 FAET27-016/2024 MNIM96-020/2023 MAET27-038/2022 F
19AET27-023/2023 FAET27-026/2024 MAET27-010/2021 MAET27-019/2023 F
20Cage 7AET27-012/2024 FAET27-033/2022 MAET27-055/2023 F7AET27-033/2024 MAET27-035/2022 MAET27-026/2022 F
21AET27-012/2024 FAET27-055/2024 MAET27-036/2023 MNIM96-045/2022 F
22AET27-012/2024 FAET27-022/2022 MAET27-049/2021 MAET27-003/2021 F
23Cage 8AET27-014/2024 FAET27-035/2022 MAET27-055/2023 F8AET27-026/2024 MAET27-010/2021 MAET27-019/2023 F
24AET27-014/2024 FAET27-043/2024 MNIM96-020/2023 MAET27-020/2022 F
25AET27-014/2024 FNIM96-008/2021 MNIM96-002/2020 MNIM96-020/2020 F
26Cage 9AET27-019/2024 FAET27-022/2022 MNIM96-010/2021 F9AET27-006/2024 MAET27-022/2023 MAET27-015/2023 F
27AET27-019/2024 FAET27-009/2024 MAET27-033/2022 MAET27-009/2023 F
28AET27-019/2024 FAET27-033/2024 MAET27-035/2022 MAET27-026/2022 F
29Cage 10AET27-023/2024 FAET27-022/2022 MNIM96-010/2021 F10AET27-057/2024 MAET27-022/2022 MNIM96-010/2021 F
30AET27-023/2024 FAET27-016/2024 MNIM96-020/2023 MAET27-038/2022 F
31AET27-023/2024 F5919-001/2023 M3024-058/2020 M3024-011/2022 F
32Cage 11AET27-035/2024 FAET27-035/2022 MAET27-026/2022 F11NIM96-020/2023 MNIM96-011/2022 MNIM96-020/2021 F
33AET27-035/2024 FAET27-022/2023 MAET27-022/2022 MNIM96-010/2021 F
34AET27-035/2024 FNIM96-008/2021 MNIM96-002/2020 MNIM96-020/2020 F
35Cage 12AET27-036/2024 FAET27-035/2022 MAET27-026/2022 F12AET27-036/2023 MNIM96-024/2021 MAET27-011/2022 F
36AET27-036/2024 FAET27-017/2024 MNIM96-020/2023 MAET27-038/2022 F
37AET27-036/2024 FAET27-035/2022 MNIM96-005/2021 MAET27-068/2021 F
38Cage 13AET27-044/2024 FNIM96-020/2023 MAET27-020/2022 F13AET27-022/2022 MAET27-049/2021 MAET27-003/2021 F
39AET27-044/2024 FAET27-036/2023 MNIM96-024/2021 MAET27-011/2022 F
40AET27-044/2024 FAET27-005/2024 MAET27-022/2023 MAET27-015/2023 F
41Cage 14AET27-046/2024 FAET27-022/2022 MAET27-017/2023 F14AET27-043/2024 MNIM96-020/2023 MAET27-020/2022 F
42AET27-046/2024 FNIM96-020/2023 MNIM96-011/2022 MNIM96-020/2021 F
43AET27-046/2024 FAET27-036/2023 MNIM96-024/2021 MAET27-011/2022 F
44Cage 15AET27-047/2024 FAET27-022/2022 MAET27-017/2023 F15AET27-033/2024 MAET27-035/2022 MAET27-026/2022 F
45AET27-047/2024 FAET27-016/2024 MNIM96-020/2023 MAET27-038/2022 F
46AET27-047/2024 FAET27-026/2024 MAET27-010/2021 MAET27-019/2023 F
47Cage 16AET27-060/2024 FNIM96-020/2023 MAET27-023/2023 F16AET27-006/2024 MAET27-022/2023 MAET27-015/2023 F
48AET27-060/2024 FAET27-007/2024 M5919-001/2023 MAET27-023/2023 F
49AET27-060/2024 FAET27-022/2023 MAET27-022/2022 MNIM96-010/2021 F
50Cage 17AET27-078/23-2024 FAET27-022/2022 MAET27-026/2022 F17AET27-033/2024 MAET27-035/2022 MAET27-026/2022 F
51AET27-078/23-2024 FAET27-006/2024 MAET27-022/2023 MAET27-015/2023 F
52AET27-078/23-2024 FAET27-005/2024 MAET27-022/2023 MAET27-015/2023 F
53Cage 18AET27-079/23-2024 FAET27-036/2023 MAET27-037/2022 F18AET27-036/2023 MNIM96-024/2021 MAET27-011/2022 F
54AET27-079/23-2024 F5919-001/2023 M3024-058/2020 M3024-011/2022 F
55AET27-079/23-2024 FAET27-022/2022 MAET27-049/2021 MAET27-003/2021 F
Crossing
Cells with Data Validation
CellAllowCriteria
F2:F55List=Elevage!$R$2:$R$22


"Crossing" sheet :

Copier Intersection COL et Lignes .xlsm
LMNOPQRSTUVWXYZAAABACAD
1Females
2MalesNIM96-010/2021 FAET27-026/2022 FAET27-037/2022 FAET27-038/2022 FAET27-017/2023 FAET27-023/2023 FAET27-012/2024 FAET27-014/2024 FAET27-019/2024 FAET27-023/2024 FAET27-035/2024 FAET27-036/2024 FAET27-044/2024 FAET27-046/2024 FAET27-047/2024 FAET27-060/2024 FAET27-078/23-2024 FAET27-079/23-2024 F
3NIM96-008/2021 M2,01%2,01%27,57%14,53%1,71%28,13%8,15%8,11%14,53%14,53%1,86%1,86%1,45%14,25%14,25%7,71%1,32%1,32%
4AET27-022/2022 M8,00%8,00%4,45%26,34%7,02%0,94%6,83%7,73%26,34%26,34%7,50%7,50%5,52%28,08%28,08%14,71%29,39%29,39%
5AET27-035/2022 M2,29%2,29%1,66%2,06%4,80%1,05%8,49%1,46%2,06%2,06%3,54%3,54%5,58%2,36%2,36%5,46%3,94%3,94%
65919-001/2023 M5,00%5,00%9,75%26,68%4,37%27,03%10,61%11,04%26,68%26,67%4,67%4,67%3,48%18,03%18,03%14,33%15,34%15,34%
7NIM96-020/2023 M28,13%28,15%15,07%5,00%6,66%2,01%5,72%28,56%5,00%5,00%28,45%28,46%17,37%11,52%11,52%5,80%7,32%7,32%
8AET27-022/2023 M0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%
9AET27-036/2023 M6,62%6,62%3,76%1,98%9,60%0,89%5,30%6,38%1,98%1,98%8,11%8,11%28,70%3,41%3,41%26,38%6,34%6,34%
10AET27-005/2024 M2,49%2,49%4,87%25,23%2,18%13,51%5,30%5,51%13,34%13,34%2,33%2,33%1,73%9,01%9,01%13,11%7,67%7,67%
11AET27-006/2024 M2,49%2,49%4,87%13,34%2,18%13,51%5,30%5,51%13,34%13,33%2,33%2,33%1,73%9,01%9,01%7,16%7,67%7,67%
12AET27-007/2024 M17,38%28,44%9,41%3,48%8,13%1,45%5,51%11,94%3,48%3,48%12,75%12,75%23,04%7,46%7,46%16,08%6,82%6,82%
13AET27-009/2024 M17,38%28,44%9,41%3,48%8,13%1,45%5,51%11,94%3,48%3,48%12,75%12,75%23,04%7,46%7,46%16,08%6,82%6,82%
14AET27-013/2024 M17,38%17,38%9,41%3,48%8,13%1,45%5,51%11,94%3,48%3,48%12,75%12,75%28,57%7,46%7,46%16,08%6,82%6,82%
15AET27-016/2024 M17,38%17,38%9,41%3,48%8,13%1,45%5,51%11,94%3,48%3,48%12,75%12,75%28,57%7,46%7,46%16,08%6,82%6,82%
16AET27-017/2024 M17,39%17,39%9,62%4,67%28,33%1,86%7,27%19,50%4,67%4,67%28,38%28,39%12,75%8,55%8,55%6,38%17,91%17,91%
17AET27-026/2024 M2,14%2,14%8,36%8,29%3,26%14,59%8,32%4,78%8,29%8,29%2,70%2,70%3,51%5,17%5,17%6,58%2,63%2,63%
18AET27-033/2024 M15,55%10,02%18,29%18,20%4,27%21,04%8,75%11,91%18,20%18,20%7,14%7,14%6,44%16,82%16,82%10,53%9,82%9,82%
19AET27-042/2024 M15,55%10,02%18,29%18,20%4,27%21,04%8,75%11,91%18,20%18,20%7,14%7,14%6,44%16,82%16,82%10,53%9,82%9,82%
20AET27-043/2024 M5,00%5,00%9,75%26,68%4,37%27,03%10,61%11,04%26,67%26,67%4,67%4,67%3,48%18,03%18,03%14,33%15,35%15,35%
21AET27-055/2024 M17,50%17,50%12,80%11,04%10,47%14,36%15,71%28,54%11,04%11,04%19,52%19,52%11,93%10,25%10,25%8,70%9,09%9,09%
22AET27-057/2024 M4,77%4,77%6,53%15,40%3,86%14,56%18,11%6,97%15,40%15,40%4,31%4,31%3,99%11,37%11,37%9,30%10,05%10,05%
23AET27-067/2024 M5,80%5,80%6,75%26,22%6,98%13,96%7,95%8,70%14,33%14,33%6,38%6,38%16,08%10,72%10,72%26,30%10,84%10,84%
New couples
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
harzer said:
In the "L" column of the "New couples" sheet, from "L3", we find the list of Males.
Click to expand...
The Males in "New couples" are in Column A not column L. If they are actually in column L, please post an updated copy of the "New couples" sheet. Please clarify.
 
Upvote 0
Hello mumps,
Thanks for your feedback.
The columns from "A" to "K" were occupied by other data that I use for other purposes, I deleted them so as not to disturb you with this data.
I am attaching a new image with the columns from "A" to "K" without any data.
Remaining at your disposal, I wish you a good evening and good programming.
Greetings.

Copier Intersection COL et Lignes .xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1Females
2MalesNIM96-010/2021 FAET27-026/2022 FAET27-037/2022 FAET27-038/2022 FAET27-017/2023 FAET27-023/2023 FAET27-012/2024 FAET27-014/2024 FAET27-019/2024 FAET27-023/2024 FAET27-035/2024 FAET27-036/2024 FAET27-044/2024 FAET27-046/2024 FAET27-047/2024 FAET27-060/2024 FAET27-078/23-2024 FAET27-079/23-2024 F
3NIM96-008/2021 M2,01%2,01%27,57%14,53%1,71%28,13%8,15%8,11%14,53%14,53%1,86%1,86%1,45%14,25%14,25%7,71%1,32%1,32%
4AET27-022/2022 M8,00%8,00%4,45%26,34%7,02%0,94%6,83%7,73%26,34%26,34%7,50%7,50%5,52%28,08%28,08%14,71%29,39%29,39%
5AET27-035/2022 M2,29%2,29%1,66%2,06%4,80%1,05%8,49%1,46%2,06%2,06%3,54%3,54%5,58%2,36%2,36%5,46%3,94%3,94%
65919-001/2023 M5,00%5,00%9,75%26,68%4,37%27,03%10,61%11,04%26,68%26,67%4,67%4,67%3,48%18,03%18,03%14,33%15,34%15,34%
7NIM96-020/2023 M28,13%28,15%15,07%5,00%6,66%2,01%5,72%28,56%5,00%5,00%28,45%28,46%17,37%11,52%11,52%5,80%7,32%7,32%
8AET27-022/2023 M0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%0,00%
9AET27-036/2023 M6,62%6,62%3,76%1,98%9,60%0,89%5,30%6,38%1,98%1,98%8,11%8,11%28,70%3,41%3,41%26,38%6,34%6,34%
10AET27-005/2024 M2,49%2,49%4,87%25,23%2,18%13,51%5,30%5,51%13,34%13,34%2,33%2,33%1,73%9,01%9,01%13,11%7,67%7,67%
11AET27-006/2024 M2,49%2,49%4,87%13,34%2,18%13,51%5,30%5,51%13,34%13,33%2,33%2,33%1,73%9,01%9,01%7,16%7,67%7,67%
12AET27-007/2024 M17,38%28,44%9,41%3,48%8,13%1,45%5,51%11,94%3,48%3,48%12,75%12,75%23,04%7,46%7,46%16,08%6,82%6,82%
13AET27-009/2024 M17,38%28,44%9,41%3,48%8,13%1,45%5,51%11,94%3,48%3,48%12,75%12,75%23,04%7,46%7,46%16,08%6,82%6,82%
14AET27-013/2024 M17,38%17,38%9,41%3,48%8,13%1,45%5,51%11,94%3,48%3,48%12,75%12,75%28,57%7,46%7,46%16,08%6,82%6,82%
15AET27-016/2024 M17,38%17,38%9,41%3,48%8,13%1,45%5,51%11,94%3,48%3,48%12,75%12,75%28,57%7,46%7,46%16,08%6,82%6,82%
16AET27-017/2024 M17,39%17,39%9,62%4,67%28,33%1,86%7,27%19,50%4,67%4,67%28,38%28,39%12,75%8,55%8,55%6,38%17,91%17,91%
17AET27-026/2024 M2,14%2,14%8,36%8,29%3,26%14,59%8,32%4,78%8,29%8,29%2,70%2,70%3,51%5,17%5,17%6,58%2,63%2,63%
18AET27-033/2024 M15,55%10,02%18,29%18,20%4,27%21,04%8,75%11,91%18,20%18,20%7,14%7,14%6,44%16,82%16,82%10,53%9,82%9,82%
19AET27-042/2024 M15,55%10,02%18,29%18,20%4,27%21,04%8,75%11,91%18,20%18,20%7,14%7,14%6,44%16,82%16,82%10,53%9,82%9,82%
20AET27-043/2024 M5,00%5,00%9,75%26,68%4,37%27,03%10,61%11,04%26,67%26,67%4,67%4,67%3,48%18,03%18,03%14,33%15,35%15,35%
21AET27-055/2024 M17,50%17,50%12,80%11,04%10,47%14,36%15,71%28,54%11,04%11,04%19,52%19,52%11,93%10,25%10,25%8,70%9,09%9,09%
22AET27-057/2024 M4,77%4,77%6,53%15,40%3,86%14,56%18,11%6,97%15,40%15,40%4,31%4,31%3,99%11,37%11,37%9,30%10,05%10,05%
23AET27-067/2024 M5,80%5,80%6,75%26,22%6,98%13,96%7,95%8,70%14,33%14,33%6,38%6,38%16,08%10,72%10,72%26,30%10,84%10,84%
New couples
 
Upvote 0
Try:
VBA Code: 
Sub BreedingRate()
    Application.ScreenUpdating = False
    Dim vF As Variant, arr() As Variant, x As Long, i As Long, srcWS As Worksheet, desWS As Worksheet, female As Range, male As Range
    Set srcWS = Sheets("New couples")
    Set desWS = Sheets("Crossing")
    vF = desWS.Range("B2", desWS.Range("B" & Rows.Count).End(xlUp)).Resize(, 5).Value
    For i = LBound(vF) To UBound(vF)
        Set female = srcWS.Rows(2).Find(vF(i, 1), LookIn:=xlValues, lookat:=xlWhole)
        Set male = srcWS.Range("L:L").Find(vF(i, 5), LookIn:=xlValues, lookat:=xlWhole)
        x = x + 1
        ReDim Preserve arr(1 To x)
        arr(x) = srcWS.Cells(male.Row, female.Column)
    Next i
    desWS.Range("I2").Resize(x) = Application.Transpose(arr)
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
Hello mumps,
Thank you for your feedback and the proposed code.
Indeed, the code works very well and meets my expectations, Thank you very much, it is really the code I was waiting for.
Can I ask you for additional code, which for good visibility, can color (for example in yellow) all the cells where the inbreeding rates are transposed in column "I".
I will try to understand your programming by putting a comment on the lines of code, hoping that I will succeed.
Thank you again and I look forward to reading you.
Greetings.
 
Upvote 0
Try:
VBA Code: 
Sub BreedingRate()
    Application.ScreenUpdating = False
    Dim vF As Variant, arr() As Variant, x As Long, i As Long, srcWS As Worksheet, desWS As Worksheet, female As Range, male As Range
    Set srcWS = Sheets("New couples")
    Set desWS = Sheets("Crossing")
    vF = desWS.Range("B2", desWS.Range("B" & Rows.Count).End(xlUp)).Resize(, 5).Value
    For i = LBound(vF) To UBound(vF)
        Set female = srcWS.Rows(2).Find(vF(i, 1), LookIn:=xlValues, lookat:=xlWhole)
        Set male = srcWS.Range("L:L").Find(vF(i, 5), LookIn:=xlValues, lookat:=xlWhole)
        x = x + 1
        ReDim Preserve arr(1 To x)
        arr(x) = srcWS.Cells(male.Row, female.Column)
    Next i
    With desWS.Range("I2").Resize(x)
        .Value = Application.Transpose(arr)
        .Interior.ColorIndex = 6
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hello mumps,
Thanks for your feedback,
I think I expressed myself badly. By the way, it is not in the "I" column of the "Crossing" sheet that I want to put the cells in yellow, it is rather in the "New couples" sheet, in the cells of the range ("M3:AD23") which represent the transposed inbreeding rates.
Greetings.
 
Upvote 0
Place this line of code:
Code: 
srcWS.Cells(male.Row, female.Column).Interior.ColorIndex=6
directly below the line beginning with Set male=
 
Upvote 0
Also undo the previous change I made in the code.
 
Upvote 0
Hello mumps,
Thanks for your feedback.
The code update suits me and satisfies me completely.
Thanks for your availability, your patience and your sharing.
Friendships.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Delete the last character of cells in column "K" if it is equal to "X" or "x"
Replies
8
Views
115
Fuji
F
H
  • Question Question
copy some columns based on the value in column "A".
Replies
9
Views
349
Cubist
Cubist
H
  • Solved
Copy rows if cells in column "K" end with either "X" or "x".
Replies
3
Views
228
rollis13
rollis13
H
  • Solved
Capitalize the last character in column “K” conditionally.
Replies
3
Views
220
Fluff
Fluff
H
  • Solved
Search for topics based on their groups.
Replies
7
Views
397
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,719
Messages
6,174,089
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top