Help with how to get the data please

[Surreyview]

Good Morning,

I have two spreadsheets:
1. Is a master file of 10,000 cutomers that contains all information such as name, ID, address, payment schedule, price charged, item brought
2. Is a file that has some information in, but has lots of missing data.

I want to use file 1 to populate file 2, specifically I want to
1. look up the patient ID file 2 and locate it in file 2
2. filter by a specific item purchased within file 2
3. and insert the price charged from file 1.

How do I do this? Is it possible?
thank you

 

[Gerald Higgins]

This is probably do-able, please post a small sample of your data, showing several different records, and explaining what exactly you want to bring across.
If your real data is secret, make up some data that looks similar.

 

[Surreyview]

Hello Gerald, thank you for your help.
I have attached my two sample data files.
Master data file: has all the clean data in.
File 2: Has some missing data, that need to get from the master data file: I specifically want to look for those patients that have a Model A (column D), identify the patient/customer by their ID (B) and insert the price (column F), that specifc customer/patient paid.
thank you

 

[Surreyview]

Sorry Gerald, how do I add the files? so you can see them?

 

[Gerald Higgins]

You can't add files to the thread.
Just post some data within the text of a post.

 

[Surreyview]

I am having trouble posting the attachments, I hope this works:
FIle 2:
[TABLE="width: 787"]
<colgroup><col width="143" style="width: 107pt; mso-width-source: userset; mso-width-alt: 5091;"> <col width="171" style="width: 128pt; mso-width-source: userset; mso-width-alt: 6087;"> <col width="206" style="width: 155pt; mso-width-source: userset; mso-width-alt: 7338;"> <col width="274" style="width: 205pt; mso-width-source: userset; mso-width-alt: 9728;"> <col width="64" style="width: 48pt;"> <col width="64" style="width: 48pt;"> <col width="64" style="width: 48pt;" span="2"> <tbody>[TR]
[TD="width: 143, bgcolor: #BDBDBD"]PO NUMBER[/TD]
[TD="width: 171, bgcolor: #BDBDBD"]PATIENT ID[/TD]
[TD="width: 206, bgcolor: #BDBDBD"]ORDER[/TD]
[TD="width: 274, bgcolor: #BDBDBD"]DESCRIPTION[/TD]
[TD="width: 64, bgcolor: #BDBDBD"]QTY[/TD]
[TD="width: 64, bgcolor: yellow"]NET[/TD]
[TD="width: 64, bgcolor: #BDBDBD"]VAT[/TD]
[TD="width: 64, bgcolor: #BDBDBD"]TOTAL[/TD]
[/TR]
[TR]
[TD="width: 143, bgcolor: transparent"]fff[/TD]
[TD="width: 171, bgcolor: transparent"]99999[/TD]
[TD="width: 206, bgcolor: transparent"]1[/TD]
[TD="width: 274, bgcolor: transparent"]Model A[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1.00[/TD]
[TD="width: 64, bgcolor: yellow, align: right"]65.00[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]13.00[/TD]
[TD="bgcolor: transparent, align: right"]78.00[/TD]
[/TR]
[TR]
[TD="width: 143, bgcolor: transparent"]22[/TD]
[TD="width: 171, bgcolor: transparent"]2222[/TD]
[TD="width: 206, bgcolor: transparent"]2[/TD]
[TD="width: 274, bgcolor: transparent"]Model A[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1.00[/TD]
[TD="width: 64, bgcolor: yellow"] [/TD]
[TD="width: 64, bgcolor: transparent, align: right"]4.00[/TD]
[TD="bgcolor: transparent, align: right"]24.00[/TD]
[/TR]
[TR]
[TD="width: 143, bgcolor: transparent"]33[/TD]
[TD="width: 171, bgcolor: transparent"]33333[/TD]
[TD="width: 206, bgcolor: transparent"]3[/TD]
[TD="width: 274, bgcolor: transparent"]Model A[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1.00[/TD]
[TD="width: 64, bgcolor: yellow, align: right"]65.00[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]13.00[/TD]
[TD="bgcolor: transparent, align: right"]78.00[/TD]
[/TR]
[TR]
[TD="width: 143, bgcolor: transparent"]44[/TD]
[TD="width: 171, bgcolor: transparent"]312[/TD]
[TD="width: 206, bgcolor: transparent"]4[/TD]
[TD="width: 274, bgcolor: transparent"]Model A[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1.00[/TD]
[TD="width: 64, bgcolor: yellow"] [/TD]
[TD="width: 64, bgcolor: transparent, align: right"]4.00[/TD]
[TD="bgcolor: transparent, align: right"]24.00[/TD]
[/TR]
[TR]
[TD="width: 143, bgcolor: transparent"]55[/TD]
[TD="width: 171, bgcolor: transparent"]3333[/TD]
[TD="width: 206, bgcolor: transparent"]5[/TD]
[TD="width: 274, bgcolor: transparent"]Model A[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1.00[/TD]
[TD="width: 64, bgcolor: yellow, align: right"]65.00[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]13.00[/TD]
[TD="bgcolor: transparent, align: right"]78.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Surreyview]

Master data:
[TABLE="width: 472"]
<colgroup><col width="64" style="width: 48pt;"> <col width="126" style="width: 94pt; mso-width-source: userset; mso-width-alt: 4465;"> <col width="178" style="width: 134pt; mso-width-source: userset; mso-width-alt: 6343;"> <col width="134" style="width: 100pt; mso-width-source: userset; mso-width-alt: 4750;"> <col width="64" style="width: 48pt;" span="2"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Patient ID[/TD]
[TD="width: 126, bgcolor: transparent"]Date[/TD]
[TD="width: 178, bgcolor: transparent"]Full Name[/TD]
[TD="width: 134, bgcolor: transparent"]Model[/TD]
[TD="width: 64, bgcolor: transparent"] Monthly Charge [/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]99999[/TD]
[TD="bgcolor: yellow, align: right"]01/03/2019[/TD]
[TD="bgcolor: yellow"]Mr A Smith[/TD]
[TD="bgcolor: yellow"]Model A[/TD]
[TD="bgcolor: yellow"] £ 65.00 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]33333[/TD]
[TD="bgcolor: yellow, align: right"]02/03/2019[/TD]
[TD="bgcolor: yellow"]Mr B Smith[/TD]
[TD="bgcolor: yellow"]Model A[/TD]
[TD="bgcolor: yellow"] £ 80.00 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]2222[/TD]
[TD="bgcolor: yellow, align: right"]03/03/2019[/TD]
[TD="bgcolor: yellow"]Mr C Smith[/TD]
[TD="bgcolor: yellow"]Model A[/TD]
[TD="bgcolor: yellow"] £ 65.00 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]3333[/TD]
[TD="bgcolor: yellow, align: right"]04/03/2019[/TD]
[TD="bgcolor: yellow"]Mr D Smith[/TD]
[TD="bgcolor: yellow"]Model A[/TD]
[TD="bgcolor: yellow"] £ 65.00 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

OK, I think I understand.

QUESTION 1 - is it POSSIBLE that a patient could have several different models that apply to them, e.g. Model A, Model B, Model C etc ?
QUESTION 2 - is it POSSIBLE that a patient could have had Model A on several different occasions, with different prices each time ?
QUESTION 3 - if answer to QUESTION 2 is YES, what do you want to do about it ?

 

[Surreyview]

Hi,

Q1- no, a patient should only have one model.
Q2- A patient may have the wrong price entered on file 2, but different patients may have different prices, or their may be a blank cell.

I want to be able to populate the price by looking up all patients with a model A (exclude any others), match the ID and populate the price

 

[Gerald Higgins]

Q1- no, a patient should only have one model.

So I think that means we can really ignore the model, and just look up the patient, yes ?
This makes it simpler, if this is correct.