multiple criteria on same row match then copy data on another sheet

S

stellarrr

New Member
Joined
Jul 21, 2023
Messages
4
Office Version
  1. 2019
Platform
  1. MacOS
I need some assistance with either an applicable formula or macro, and have been struggling to find a solution on my own.

I have sheet 'A' which has 20k rows of data. To simplify it, column A has a subject ID, column B has a time point, and column C has an assay value. There are 900 different subjects, and 50 different time points. Most of the subjects have a different number of time points.

In sheet 'B' I have the 900 subject IDs listed in Column A, starting at A2, and the 50 different time points listed across row 1 (A1-AW1)

I would like to be able to either use a formula or macro for each cell to check to see if in sheet A if a subject ID in column A matches the subject ID in sheet B column A AND if there is a timepoint on that row that also matches the timepoint from sheet B then I would like the value from the same row in column C on sheet A to be copied into that well.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Please clarify a bit..
Sheet A
  • Col A - contains a subject ID
  • Col B - timepoint
  • Col C - assay value
Sheet B are your lookup Tables
  • Table 1 - Col A, Row 2 thru 900+ - subject ID list Range A2:A900
  • Table 2 - Row 1, Column A to ... next 49 adjacent columns - timepoints
When there is a Match of Subject ID from SheetA:Col_A (from Table 1), and Match of SheetA:ColB (from Table 2)

Here is where clarification is needed

You appear to want the Assay value from Col C copied somewhere, but you said "copied into that well". Do you want put the Assay value?
Can you create a mini-sheet of the first few rows of SheetA and SheetB?
 
Upvote 0
ExampleData.xlsx
ABC
1Subject IDTime PointAssay Result
2436Undetermined
34315Undetermined
4431339.9
5435Undetermined
6437Undetermined
74316Undetermined
8439Undetermined
94319Undetermined
10432128.5
11434Undetermined
12438Undetermined
134320Undetermined
144317Undetermined
154310Undetermined
164314Undetermined
17431141.2
18431218.0
1918912Undetermined
2018915Undetermined
211899Undetermined
2218921Undetermined
231894Undetermined
241896Undetermined
2514313Undetermined
2614312Undetermined
2714318Undetermined
2814316Undetermined
2914315Undetermined
3014321Undetermined
311437Undetermined
321435Undetermined
3314323Undetermined
341436Undetermined
351439Undetermined
3614311Undetermined
3714327Undetermined
3814326Undetermined
3914310Undetermined
401438Undetermined
4114317Undetermined
4213618Undetermined
4313611Undetermined
4413626Undetermined
4513625Undetermined
4613612Undetermined
471362734.3
481361536.5
4913643Undetermined
5013620Undetermined
511365Undetermined
521366Undetermined
531364Undetermined
5413628Undetermined
551361626.2
561362418.7
57136937.5
5813631Undetermined
5913629Undetermined
6013622Undetermined
6113639Undetermined
621367Undetermined
6313630Undetermined
6413610Undetermined
6513617Undetermined
6613633Undetermined
6713637Undetermined
6813613Undetermined
692068Undetermined
7020617Undetermined
712064Undetermined
722069Undetermined
7320629Undetermined
7420620Undetermined
7520621Undetermined
7620614Undetermined
7720622Undetermined
7820627Undetermined
792065Undetermined
802066Undetermined
8120619Undetermined
8220618Undetermined
8320615Undetermined
8420628Undetermined
8520611Undetermined
8620624Undetermined
8720613Undetermined
8820612Undetermined
8920616Undetermined
9020623Undetermined
9120610Undetermined
9220625Undetermined
9320626Undetermined
942067Undetermined
958345Undetermined
9622522Undetermined
972257Undetermined
9822529Undetermined
9922515Undetermined
10022528Undetermined
10122527Undetermined
10222513Undetermined
10322512Undetermined
1042253436.4
10522545Undetermined
10622526Undetermined
1072253318.8
10822530Undetermined
1092256Undetermined
1102259Undetermined
11122536Undetermined
11222532Undetermined
11322510Undetermined
1142253838.7
1152252135.9
1162255Undetermined
11722531Undetermined
11822544Undetermined
11922525Undetermined
12022542Undetermined
12122514Undetermined
12222518Undetermined
12322523Undetermined
1242254Undetermined
12522516Undetermined
12622517Undetermined
1272258Undetermined
1283487Undetermined
1293484Undetermined
13034811Undetermined
13134815Undetermined
13234810Undetermined
13334812Undetermined
134348633.6
13534816Undetermined
1363485Undetermined
1373488Undetermined
13834814Undetermined
13934818Undetermined
1403489Undetermined
14134813Undetermined
14237817Undetermined
14337811Undetermined
14437810Undetermined
14537830Undetermined
14637840Undetermined
14737831Undetermined
14837812Undetermined
14937828Undetermined
15037819Undetermined
15137838Undetermined
15237839Undetermined
1533784Undetermined
15437847Undetermined
1553785Undetermined
15637823Undetermined
15737825Undetermined
15837829Undetermined
15937841Undetermined
16037815Undetermined
1613788Undetermined
16237836Undetermined
16337844Undetermined
16437818Undetermined
16537843Undetermined
16637827Undetermined
16737826Undetermined
16837821Undetermined
16937842Undetermined
17037834Undetermined
17137837Undetermined
17237816Undetermined
17337846Undetermined
17437824Undetermined
17537814Undetermined
17637835Undetermined
17737813Undetermined
17837822Undetermined
1793786Undetermined
18037832Undetermined
1813787Undetermined
18236036Undetermined
18336018Undetermined
18436021Undetermined
1853606Undetermined
18636027Undetermined
18736029Undetermined
18836030Undetermined
18936016Undetermined
19036025Undetermined
19136024Undetermined
19236033Undetermined
1933608Undetermined
19436015Undetermined
19536020Undetermined
19636026Undetermined
19736023Undetermined
19836032Undetermined
19936028Undetermined
20036038Undetermined
A


ExampleData.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASAT
1Time Point34567891011121314151617181920212223242526272829303132333435363738394041424344454647
2Subject ID
343
4189
5143
6136
7206
8834
9225
10348
11378
12360
B
 
Upvote 0
Bosquedeguate said:
Please clarify a bit..
Sheet A
  • Col A - contains a subject ID
  • Col B - timepoint
  • Col C - assay value
Sheet B are your lookup Tables
  • Table 1 - Col A, Row 2 thru 900+ - subject ID list Range A2:A900
  • Table 2 - Row 1, Column A to ... next 49 adjacent columns - timepoints
When there is a Match of Subject ID from SheetA:Col_A (from Table 1), and Match of SheetA:ColB (from Table 2)

Here is where clarification is needed

You appear to want the Assay value from Col C copied somewhere, but you said "copied into that well". Do you want put the Assay value?
Can you create a mini-sheet of the first few rows of SheetA and SheetB?
Click to expand...
Mini-sheet attached in a separate response.

But I am hoping it could be copied over like the value in A!C2 to B!E3 and the value in A!C4 to B!L3.
 
Upvote 0
stellarrr said:
like the value in A!C2 to B!E3 and the value in A!C4 to B!L3.
Click to expand...
Something like this perhaps?
Book1
ABCDEFGHIJKL
1Time Point345678910111213
2Subject ID
343 UndeterminedUndeterminedUndeterminedUndeterminedUndeterminedUndeterminedUndetermined41.218.039.9
4189 Undetermined Undetermined  Undetermined  Undetermined 
5143  UndeterminedUndeterminedUndeterminedUndeterminedUndeterminedUndeterminedUndeterminedUndeterminedUndetermined
6136 UndeterminedUndeterminedUndeterminedUndetermined 37.5UndeterminedUndeterminedUndeterminedUndetermined
7206 UndeterminedUndeterminedUndeterminedUndeterminedUndeterminedUndeterminedUndeterminedUndeterminedUndeterminedUndetermined
8834  Undetermined        
9225 UndeterminedUndeterminedUndeterminedUndeterminedUndeterminedUndeterminedUndetermined UndeterminedUndetermined
10348 UndeterminedUndetermined33.6UndeterminedUndeterminedUndeterminedUndeterminedUndeterminedUndeterminedUndetermined
11378 UndeterminedUndeterminedUndeterminedUndeterminedUndetermined UndeterminedUndeterminedUndeterminedUndetermined
12360   Undetermined Undetermined     
B
Cell Formulas
RangeFormula
B3:L12B3=IFERROR(INDEX(A!$C$2:$C$200,MATCH(1,($A3=A!$A$2:$A$200)*(B$1=A!$B$2:$B$200),0)),"")
 
Upvote 1
Solution
You must log in or register to reply here.

Similar threads

E
  • Question Question
Write Macro to highlight based on values in another sheet
Replies
3
Views
328
Peter_SSs
Peter_SSs
R
  • Question Question
VBA Generate email with multiple data in body.
Replies
2
Views
133
Logit
Logit
L
  • Question Question
How to look up data from on sheet to another when both sheets have multiple cells with the same ID number.
Replies
5
Views
295
hagia_sofia
hagia_sofia
D
  • Solved
Using Vlookup, If statements??
Replies
3
Views
228
Dprobasco
D
S
  • Question Question
Combining VLOOKUP with removing/blanking the result if it is a duplicate of another cell in the same row
Replies
9
Views
249
hagia_sofia
hagia_sofia

Forum statistics

Threads
1,224,823
Messages
6,181,185
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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