Comparing 4 columns and return to another

danieleus

New Member
Joined
Nov 23, 2009
Messages
2
Hello all,

I'm new on this forum and i would like to ask you for a solution to "my problem" hopefully you will help :confused:.
i have 2 worksheets each of them containing a table with number of invoice quantity and currency, the table from the first worksheet was modified by deleting some rows and put in the second worksheet.
Unfortunately from the second worksheet somebody deleted a row with currency values and now i have to find out a way to put back the currency for each invoice.
Below is a small example column A number of invoice B quantity C currency
<table style="border-collapse: collapse; width: 162pt;" width="215" border="0" cellpadding="0" cellspacing="0"><col style="width: 62pt;" width="82"> <col style="width: 38pt;" width="51"> <col style="width: 62pt;" width="82"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; width: 62pt;" width="82" height="17">69110469</td> <td class="xl66" style="width: 38pt;" width="51">800</td> <td class="xl67" style="width: 62pt;" width="82"> 1.548,00 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">69110469</td> <td class="xl66">600</td> <td class="xl67"> 1.515,20 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">69110466</td> <td class="xl66">180</td> <td class="xl67"> 1.823,40 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">69110466</td> <td class="xl66">280</td> <td class="xl67"> 1.825,20 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">69110463</td> <td class="xl66">200</td> <td class="xl67"> 605,23 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">69110462</td> <td class="xl66">400</td> <td class="xl67"> 1.709,04 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">69110467</td> <td class="xl66">120</td> <td class="xl67"> 867,84 </td> </tr> </tbody></table>The problem is that as you can see i have invoices with the same number the only solution i see is to compare the number of invoice and quantity from the second worksheet with this one and to return the corresponding currency value in a new column.
Could you please help with proper formula or with an advice.
Thank you
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

BGY23

Well-known Member
Joined
Aug 13, 2008
Messages
515
Hi,
I assume you have a table with all the invoices and values in ? Only you say you deleted some rows and put them on the second sheet ? If you deleted them then formula's aren't going to help ?

Assuming you copied them or that at least you have a list of those invoices that are on sheet 2 on a separate sheet and with the values on.

You can limit your problem by "joining" the invoice number and quantity on sheet 1 and doing a lookup in sheet 2.

How?
1 insert a new column on sheet 1 at "A". Your Inv no., Qty and value should now be in Columns B,C and D.

2 In cell A1 write this formula =B1&C1. This will join your invoice number and your quantity.

3 Assuming your invoice no and quantity are in columns A and B in sheet 2 then in C1 write this formula <TABLE style="WIDTH: 233pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=311 border=0 x:str><COLGROUP><COL style="WIDTH: 233pt; mso-width-source: userset; mso-width-alt: 11373" width=311><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 233pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=311 height=17 x:str="'=VLOOKUP(A1&B1,Sheet1!$A$1:$D$16,4,FALSE)">=VLOOKUP(A1&B1,Sheet1!$A$1:$D$16,4,FALSE)</TD></TR></TBODY></TABLE>

You will only have problems where the quantity on an invoice is duplicated.

Good luck
 

danieleus

New Member
Joined
Nov 23, 2009
Messages
2
Thank you very much BGY23 it worked perfect :biggrin:. Only few rows to check manually.
Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,126,984
Messages
5,621,981
Members
415,871
Latest member
Frank_Aa

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
Top