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

H

harzer

Board Regular
Joined
Dec 15, 2021
Messages
159
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
 
Sort by date Sort by votes

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
162
Fuji
F
H
  • Question Question
copy some columns based on the value in column "A".
Replies
9
Views
365
Cubist
Cubist
H
  • Solved
Copy rows if cells in column "K" end with either "X" or "x".
Replies
3
Views
235
rollis13
rollis13
H
  • Solved
Capitalize the last character in column “K” conditionally.
Replies
3
Views
233
Fluff
Fluff
H
  • Solved
Search for topics based on their groups.
Replies
7
Views
405
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,749
Messages
6,180,730
Members
452,995
Latest member
isldboy

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