Related:
- Do I want to use the vlookup?
- Transfer data from one excel worksheet to another automatically vlookup - Guide
- Vlookup if two cells match return value from third ✓ - Excel Forum
- Vlookup if cell contains a word within text ✓ - Word Forum
- Vlookup false ✓ - Excel Forum
- Vlookup to find missing data in 2 columns - Excel Forum
4 responses
The order of the data on both sheets are the same, so you could just use copy/paste.
But I guess this is not always the case.
I noticed that the data separating the pigs are the dates.
You could use Vlookup to retreive the remaining data.
Put the following formula in cell E2 on sheet 2
=Vlookup(Sheet2!D2,Sheet1!$B$2:$G$8,2) Change the 8 from $G$8 to the number of your last row.
Drag the formula down as far as necessary.
Select and copy the fomula and paste it into cell F2. Then add one on the last number:
=Vlookup(Sheet2!D2,Sheet1!$B$2:$G$8,3) and drag the formula down.
If you drag the formula to the right be sure that the first part of the fomula is still Sheet2!D2.
Repeat for the remaining 3 columns.
G2: =Vlookup(Sheet2!D2,Sheet1!$B$2:$G$8,4) and drag the formula down.
H2: =Vlookup(Sheet2!D2,Sheet1!$B$2:$G$8,5) and drag the formula down.
I2: =Vlookup(Sheet2!D2,Sheet1!$B$2:$G$8,6) and drag the formula down.
Best regards,
Trowa
But I guess this is not always the case.
I noticed that the data separating the pigs are the dates.
You could use Vlookup to retreive the remaining data.
Put the following formula in cell E2 on sheet 2
=Vlookup(Sheet2!D2,Sheet1!$B$2:$G$8,2) Change the 8 from $G$8 to the number of your last row.
Drag the formula down as far as necessary.
Select and copy the fomula and paste it into cell F2. Then add one on the last number:
=Vlookup(Sheet2!D2,Sheet1!$B$2:$G$8,3) and drag the formula down.
If you drag the formula to the right be sure that the first part of the fomula is still Sheet2!D2.
Repeat for the remaining 3 columns.
G2: =Vlookup(Sheet2!D2,Sheet1!$B$2:$G$8,4) and drag the formula down.
H2: =Vlookup(Sheet2!D2,Sheet1!$B$2:$G$8,5) and drag the formula down.
I2: =Vlookup(Sheet2!D2,Sheet1!$B$2:$G$8,6) and drag the formula down.
Best regards,
Trowa
So if the data on sheet 2 in column B & D is the same as sheet 1's column S & W you would like in another column to be put "male" (for example) and "female" if they are not the same.
=IF(sheet2!B1=sheet1!S1,IF(sheet2!D1=sheet1!W1,"Male","Female"),"Female")
I hope I understood you correctly.
Kind regards,
Trowa
=IF(sheet2!B1=sheet1!S1,IF(sheet2!D1=sheet1!W1,"Male","Female"),"Female")
I hope I understood you correctly.
Kind regards,
Trowa
Hi Trowa thanks for responding.
I actually need it to return the value that is in column 29. The sex would be Barrow, Guilt, Sow & Boar. So instead of one or the other it would actually need the result in that column.
I hope that makes sense.
Thanks again
I actually need it to return the value that is in column 29. The sex would be Barrow, Guilt, Sow & Boar. So instead of one or the other it would actually need the result in that column.
I hope that makes sense.
Thanks again
Hi Amstarr,
You told that there are two conditions, one where the data matches and one where they don't.
Based on that I assumed there are two sexes.
Now you say there are 4.
My question to you is what the other conditions are?
In which case would the result be Barrow?
In which case would the result be Guilt?
In which case would the result be Sow?
In which case would the result be Boar?
To clarify the situation, maybe you could give me an example of how your sheet looks like.
Best regards,
Trowa
You told that there are two conditions, one where the data matches and one where they don't.
Based on that I assumed there are two sexes.
Now you say there are 4.
My question to you is what the other conditions are?
In which case would the result be Barrow?
In which case would the result be Guilt?
In which case would the result be Sow?
In which case would the result be Boar?
To clarify the situation, maybe you could give me an example of how your sheet looks like.
Best regards,
Trowa
Sheet 1
A B C D E F G
1 Ear Tag Test Date Age Sex Sire Dam SOD
2 68 6/18/2008 4.1 Sow Sisu 4 Cher 4 Karlstorp 8
3 68 4/23/2008 1.9 Boar Karlstorp Cher 5 Karlstorp 1
4 68 4/11/2008 3.6 Sow Iron Man 6 Kate 3 Karlstorp 8
5 69 6/19/2008 3.1 Sow Samson 2 Cher 3 Pikkus 6
6 69 4/11/2008 5.2 Sow Pikkus 6 Special 2 Karlstorp 8
7 70 6/10/2008 2.6 Sow Samson 2 Kate 3 Karlstorp 8
8 70 5/21/2008 3.4 Sow Pikkus 6 Cher 4 Karlstorp 9
Sheet 2
A B C D E F G H I
1 Pig # Tag # Test Result Test Date Age Sex Sire Dam SOD
2 772 68 Good 6/18/2008
3 774 68 Poor 4/23/2008
4 778 68 Excellent 4/11/2008
5 779 69 Good 6/19/2008
6 780 69 Good 4/11/2008
7 782 70 Excellent 6/10/2008
8 783 70 Poor 5/21/2008
On sheet 1 I named the table array Ear_Tag.
This is information that is provided to me from the farmers.
Note that there are sometimes more the 1 pig with the same tag number. The only way to tell them apart is by the Test Date.
On Sheet
Is where I record the test results.
I dont want to have to look up each pig after each test and fill in the rest of the cells manually. I would like to have a look up of the ear tag and the test date to find the correct pig. Then each of the cells will fill in the correct information.
I hope that clarifies the question better.
Thanks
A B C D E F G
1 Ear Tag Test Date Age Sex Sire Dam SOD
2 68 6/18/2008 4.1 Sow Sisu 4 Cher 4 Karlstorp 8
3 68 4/23/2008 1.9 Boar Karlstorp Cher 5 Karlstorp 1
4 68 4/11/2008 3.6 Sow Iron Man 6 Kate 3 Karlstorp 8
5 69 6/19/2008 3.1 Sow Samson 2 Cher 3 Pikkus 6
6 69 4/11/2008 5.2 Sow Pikkus 6 Special 2 Karlstorp 8
7 70 6/10/2008 2.6 Sow Samson 2 Kate 3 Karlstorp 8
8 70 5/21/2008 3.4 Sow Pikkus 6 Cher 4 Karlstorp 9
Sheet 2
A B C D E F G H I
1 Pig # Tag # Test Result Test Date Age Sex Sire Dam SOD
2 772 68 Good 6/18/2008
3 774 68 Poor 4/23/2008
4 778 68 Excellent 4/11/2008
5 779 69 Good 6/19/2008
6 780 69 Good 4/11/2008
7 782 70 Excellent 6/10/2008
8 783 70 Poor 5/21/2008
On sheet 1 I named the table array Ear_Tag.
This is information that is provided to me from the farmers.
Note that there are sometimes more the 1 pig with the same tag number. The only way to tell them apart is by the Test Date.
On Sheet
Is where I record the test results.
I dont want to have to look up each pig after each test and fill in the rest of the cells manually. I would like to have a look up of the ear tag and the test date to find the correct pig. Then each of the cells will fill in the correct information.
I hope that clarifies the question better.
Thanks
Sep 25, 2009 at 08:07 AM