Help with how to get the data please

Surreyview

New Member
Joined
May 23, 2019
Messages
15
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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.
 
Upvote 0
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
 
Upvote 0
I am having trouble posting the attachments, I hope this works:
FIle 2:
PO NUMBERPATIENT IDORDERDESCRIPTIONQTYNETVATTOTAL
fff999991Model A1.0065.0013.0078.00
2222222Model A1.00 4.0024.00
33333333Model A1.0065.0013.0078.00
443124Model A1.00 4.0024.00
5533335Model A1.0065.0013.0078.00
<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> </tbody>
 
Upvote 0
Master data:
Patient IDDateFull NameModel Monthly Charge
9999901/03/2019Mr A SmithModel A £ 65.00
3333302/03/2019Mr B SmithModel A £ 80.00
222203/03/2019Mr C SmithModel A £ 65.00
333304/03/2019Mr D SmithModel A £ 65.00
<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> </tbody>
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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