If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to match to Worksheets
Hi,
Here is my problem. I have two Sheets with the following data: sheet 1 sheet2 country sales country sales US 100 Spain US 300 Spain US 400 Spain France 500 US France 600 US France 700 US Germany 200 US Germany 150 US Germany 350 France France France France France France Germany Germany Germany I want to match to each country in sheet 2 the sales in sheet1. if i use Vlookup i'll always get the first result(i.e for US i'll get 100, for France 500...). if US appears more times in sheet 2 than in sheet1, I want the cell in sheet2 to be empty. please advise how could i do it? thanks, David Messika |
#2
|
|||
|
|||
How to match to Worksheets
On Wed, 17 Dec 2008 08:52:07 -0800, ??? ?????
wrote: Hi, Here is my problem. I have two Sheets with the following data: sheet 1 sheet2 country sales country sales US 100 Spain US 300 Spain US 400 Spain France 500 US France 600 US France 700 US Germany 200 US Germany 150 US Germany 350 France France France France France France Germany Germany Germany I want to match to each country in sheet 2 the sales in sheet1. if i use Vlookup i'll always get the first result(i.e for US i'll get 100, for France 500...). if US appears more times in sheet 2 than in sheet1, I want the cell in sheet2 to be empty. please advise how could i do it? thanks, David Messika If your country and sales in Sheet1 are in columns A and B respectively starting on row 2 (below one header row) and your country and sales in Sheet2 are also in columns A and B respectively starting on row 2 (below one header row) you may try the following formula in cell B2 of Sheet2: =IF(COUNTIF(A$1:A1,A2)=COUNTIF(Sheet1!A$1:A$100,A 2),"",INDEX(Sheet1!$B$1:$B$100,MATCH(A2,Sheet1!A$1 :A$100,0)+COUNTIF(A$1:A1,A2))) Adjust the 100 in three places to cover the rows in Sheet1 and copy the formula down as far as you have countries in column A of Sheet2 Note: For this formula to work I have assumed that the all sales for a country are grouped together on Sheet1 as this was the case in your example. If that is not the case, this formula will not work. Hope this helps / Lars-Åke |
#3
|
|||
|
|||
How to match to Worksheets
From your example, US have multiple sales with different amount, which sales
amount do you want to show in Sheet2 under sales. -- If this posting was helpful, please click on the Yes button Thank You cheers, "דוד מסיקה" wrote: Hi, Here is my problem. I have two Sheets with the following data: sheet 1 sheet2 country sales country sales US 100 Spain US 300 Spain US 400 Spain France 500 US France 600 US France 700 US Germany 200 US Germany 150 US Germany 350 France France France France France France Germany Germany Germany I want to match to each country in sheet 2 the sales in sheet1. if i use Vlookup i'll always get the first result(i.e for US i'll get 100, for France 500...). if US appears more times in sheet 2 than in sheet1, I want the cell in sheet2 to be empty. please advise how could i do it? thanks, David Messika |
#4
|
|||
|
|||
How to match to Worksheets
Thanks for you help
-- בברכה, דוד מסיקה "Lars-Åke Aspelin" wrote: On Wed, 17 Dec 2008 08:52:07 -0800, ??? ????? wrote: Hi, Here is my problem. I have two Sheets with the following data: sheet 1 sheet2 country sales country sales US 100 Spain US 300 Spain US 400 Spain France 500 US France 600 US France 700 US Germany 200 US Germany 150 US Germany 350 France France France France France France Germany Germany Germany I want to match to each country in sheet 2 the sales in sheet1. if i use Vlookup i'll always get the first result(i.e for US i'll get 100, for France 500...). if US appears more times in sheet 2 than in sheet1, I want the cell in sheet2 to be empty. please advise how could i do it? thanks, David Messika If your country and sales in Sheet1 are in columns A and B respectively starting on row 2 (below one header row) and your country and sales in Sheet2 are also in columns A and B respectively starting on row 2 (below one header row) you may try the following formula in cell B2 of Sheet2: =IF(COUNTIF(A$1:A1,A2)=COUNTIF(Sheet1!A$1:A$100,A 2),"",INDEX(Sheet1!$B$1:$B$100,MATCH(A2,Sheet1!A$1 :A$100,0)+COUNTIF(A$1:A1,A2))) Adjust the 100 in three places to cover the rows in Sheet1 and copy the formula down as far as you have countries in column A of Sheet2 Note: For this formula to work I have assumed that the all sales for a country are grouped together on Sheet1 as this was the case in your example. If that is not the case, this formula will not work. Hope this helps / Lars-Åke |
Thread Tools | |
Display Modes | |
|
|