Need formula to look at two cells that are the same, and extrapolate data from another cell.

W

whovis

New Member
Joined
May 27, 2014
Messages
27
Office Version
  1. 365
Platform
  1. Windows
I am trying to find a formula that will look at identical data in once column (such as an accession number), then look at data in the same rows but in a different column, and pull that data out into another column, I am trying to identify the name of the phlebotomist. The first 4 rows are the same patient. The same phlebotomist drew all 4 tubes of blood, but one is "NUR". I need to know that in this example "NUR" is "BROWN, CHARLIE"

The data i look at is extremely large. Here is an example:

TestIDAccession #Phlebotomist
HIV12A917009168BROWN, CHARLIE
RPRA917009168BROWN, CHARLIE
REDXA917009168NUR
VENIPA917009168BROWN, CHARLIE
CTGCA917009169LEPEW, PEPPY
CXURNA917009210LEGHORN, FOGHORN
FLCD4A917009305FUDD, ELMER
RCA917009748DUCK, DAFFY
RENALA917010128PIG, PORKY
OSMOA917010129CAT, TOM
GRNXA917010129NUR
MGA917010159DISNEY, WALT
APTTA917010197MOUSE, MINNIE
BLUX2A917010197NUR
DGNAXA917010197NUR
VENIPA917010197DUCK, DAFFY
CBCBOA918001550BUNNY. BUGS
CBCWDA918001550BUNNY. BUGS
CMPA918001550BUNNY. BUGS
CRPA918001550BUNNY. BUGS
MGA918001550BUNNY. BUGS
PHOSA918001550BUNNY. BUGS
SFLGSA918001550BUNNY. BUGS
TRIGA918001550BUNNY. BUGS
GRNXA918001550NUR
VENIPA918001550DUCK, DAFFY

I can sort by accession number to lump them all together. I need to formula to look the accession number (A917009168 in the first four rows), use the data "NUR" in row 3, and return the name in either row 1,2 or 4 (since the all are under the same accession number.
I appreciate any help.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Fluff said:
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Click to expand...
Updated. Sorry. Windows and 365.
 
Upvote 0
Thanks for that.
How about
+Fluff 1.xlsm
ABCD
1TestIDAccession #Phlebotomist
2HIV12A917009168BROWN, CHARLIEBROWN, CHARLIE
3RPRA917009168BROWN, CHARLIEBROWN, CHARLIE
4REDXA917009168NURBROWN, CHARLIE
5VENIPA917009168BROWN, CHARLIEBROWN, CHARLIE
6CTGCA917009169LEPEW, PEPPYLEPEW, PEPPY
7CXURNA917009210LEGHORN, FOGHORNLEGHORN, FOGHORN
8FLCD4A917009305FUDD, ELMERFUDD, ELMER
9RCA917009748DUCK, DAFFYDUCK, DAFFY
10RENALA917010128PIG, PORKYPIG, PORKY
11OSMOA917010129CAT, TOMCAT, TOM
12GRNXA917010129NURCAT, TOM
13MGA917010159DISNEY, WALTDISNEY, WALT
14APTTA917010197MOUSE, MINNIEMOUSE, MINNIE
15BLUX2A917010197NURMOUSE, MINNIE
16DGNAXA917010197NURMOUSE, MINNIE
17VENIPA917010197DUCK, DAFFYDUCK, DAFFY
18CBCBOA918001550BUNNY. BUGSBUNNY. BUGS
19CBCWDA918001550BUNNY. BUGSBUNNY. BUGS
20CMPA918001550BUNNY. BUGSBUNNY. BUGS
21CRPA918001550BUNNY. BUGSBUNNY. BUGS
22MGA918001550BUNNY. BUGSBUNNY. BUGS
23PHOSA918001550BUNNY. BUGSBUNNY. BUGS
24SFLGSA918001550BUNNY. BUGSBUNNY. BUGS
25TRIGA918001550BUNNY. BUGSBUNNY. BUGS
26GRNXA918001550NURBUNNY. BUGS
27VENIPA918001550DUCK, DAFFYDUCK, DAFFY
Lists
Cell Formulas
RangeFormula
D2:D27D2=IF(C2="NUR",INDEX(FILTER($C$2:$C$100,($B$2:$B$100=B2)*($C$2:$C$100<>"NUR")),1),C2)
 
Upvote 0
Solution
Fluff said:
Thanks for that.
How about
+Fluff 1.xlsm
ABCD
1TestIDAccession #Phlebotomist
2HIV12A917009168BROWN, CHARLIEBROWN, CHARLIE
3RPRA917009168BROWN, CHARLIEBROWN, CHARLIE
4REDXA917009168NURBROWN, CHARLIE
5VENIPA917009168BROWN, CHARLIEBROWN, CHARLIE
6CTGCA917009169LEPEW, PEPPYLEPEW, PEPPY
7CXURNA917009210LEGHORN, FOGHORNLEGHORN, FOGHORN
8FLCD4A917009305FUDD, ELMERFUDD, ELMER
9RCA917009748DUCK, DAFFYDUCK, DAFFY
10RENALA917010128PIG, PORKYPIG, PORKY
11OSMOA917010129CAT, TOMCAT, TOM
12GRNXA917010129NURCAT, TOM
13MGA917010159DISNEY, WALTDISNEY, WALT
14APTTA917010197MOUSE, MINNIEMOUSE, MINNIE
15BLUX2A917010197NURMOUSE, MINNIE
16DGNAXA917010197NURMOUSE, MINNIE
17VENIPA917010197DUCK, DAFFYDUCK, DAFFY
18CBCBOA918001550BUNNY. BUGSBUNNY. BUGS
19CBCWDA918001550BUNNY. BUGSBUNNY. BUGS
20CMPA918001550BUNNY. BUGSBUNNY. BUGS
21CRPA918001550BUNNY. BUGSBUNNY. BUGS
22MGA918001550BUNNY. BUGSBUNNY. BUGS
23PHOSA918001550BUNNY. BUGSBUNNY. BUGS
24SFLGSA918001550BUNNY. BUGSBUNNY. BUGS
25TRIGA918001550BUNNY. BUGSBUNNY. BUGS
26GRNXA918001550NURBUNNY. BUGS
27VENIPA918001550DUCK, DAFFYDUCK, DAFFY
Lists
Cell Formulas
RangeFormula
D2:D27D2=IF(C2="NUR",INDEX(FILTER($C$2:$C$100,($B$2:$B$100=B2)*($C$2:$C$100<>"NUR")),1),C2)
Click to expand...
OMG.. Brilliant. I can make this work. I adjusted the ranges to include all my data (through like column CB, and row 10000, changed the reference cells and pasted.. Ran it down the entire spreadsheet and it pulled the data.

Thanks so much for your help!!!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
Fluff said:
You're welcome & thanks for the feedback.
Click to expand...
Absolutely. got a quick question. When using this formula with 30000+ rows, it takes some times, and locks up the system. Should I/Could I sort it any particular way to help with the indexing?
 
Upvote 0
If you sort the data so that all the Accession # are together & so that NUR is never the first value, then yes.
 
Upvote 0
Fluff said:
If you sort the data so that all the Accession # are together & so that NUR is never the first value, then yes.
Click to expand...
Like NUR in row 1, or NUR in the top row of every assorted accession?
 
Upvote 0
No, NUR should never be in the 1st row of each number
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

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