Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Look Up Formula Help Needed
I Have a sheet with store and Region
Store coloum has unique value and each store number will show in only one Reg (Reg1, Reg2, Reg3, Reg4 or Reg5) for eg. If Store 7010 will be under Reg3 - it wont appear in any other Reg. I want to use a formula which willl show what stores comes in what Reg. Store Reg1 Reg2 Reg3 Reg4 9562 5602 5640 6002 6357 8352 5603 5641 6003 6358 7805 5604 5644 6004 6359 8362 5605 5645 6005 6361 5675 5607 5646 6006 6362 6378 5609 5651 6008 6363 5821 5610 5652 6009 6364 6441 5611 5664 7010 6368 7010 5613 5682 6012 6369 7508 5614 5685 6013 6370 7804 5615 5686 6015 6371 7803 5616 5688 6016 6372 7534 5617 5689 6017 6373 7504 5618 5691 6018 6374 5818 5619 6440 6019 6375 Thanks in advance Deepak |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Look Up Formula Help Needed
Hi,
Please restate your question. Show us what the original data looks like. The sample you gave us doesn't match your description of the source data or the results you want. Note that in the sample the first column is called Store and the next four columns are regions. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Deepak" wrote: I Have a sheet with store and Region Store coloum has unique value and each store number will show in only one Reg (Reg1, Reg2, Reg3, Reg4 or Reg5) for eg. If Store 7010 will be under Reg3 - it wont appear in any other Reg. I want to use a formula which willl show what stores comes in what Reg. Store Reg1 Reg2 Reg3 Reg4 9562 5602 5640 6002 6357 8352 5603 5641 6003 6358 7805 5604 5644 6004 6359 8362 5605 5645 6005 6361 5675 5607 5646 6006 6362 6378 5609 5651 6008 6363 5821 5610 5652 6009 6364 6441 5611 5664 7010 6368 7010 5613 5682 6012 6369 7508 5614 5685 6013 6370 7804 5615 5686 6015 6371 7803 5616 5688 6016 6372 7534 5617 5689 6017 6373 7504 5618 5691 6018 6374 5818 5619 6440 6019 6375 Thanks in advance Deepak |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Look Up Formula Help Needed
The actual file
Store R1 R2 R3 9562 5602 5640 8352 8352 8362 5641 6003 7805 5604 9562 6004 8362 5821 5645 6005 5675 5607 5646 6441 6378 5609 5651 7805 5821 5675 5652 6009 6441 7804 5664 6010 7010 5613 5682 6378 7508 5614 7508 6013 7804 5615 7010 6015 The Result should be as below Store R1 R2 R3 9562 ---- ---- 8352 8352 8362 ---- ---- 7805 ---- 9562 ---- 8362 5821 ---- ---- 5675 ---- ---- 6441 6378 ---- ---- 7805 5821 5675 ---- ---- 6441 7804 ---- ---- 7010 ---- ---- 6378 7508 ---- 7508 ---- 7804 ---- 7010 ---- "Shane Devenshire" wrote: Hi, Please restate your question. Show us what the original data looks like. The sample you gave us doesn't match your description of the source data or the results you want. Note that in the sample the first column is called Store and the next four columns are regions. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Deepak" wrote: I Have a sheet with store and Region Store coloum has unique value and each store number will show in only one Reg (Reg1, Reg2, Reg3, Reg4 or Reg5) for eg. If Store 7010 will be under Reg3 - it wont appear in any other Reg. I want to use a formula which willl show what stores comes in what Reg. Store Reg1 Reg2 Reg3 Reg4 9562 5602 5640 6002 6357 8352 5603 5641 6003 6358 7805 5604 5644 6004 6359 8362 5605 5645 6005 6361 5675 5607 5646 6006 6362 6378 5609 5651 6008 6363 5821 5610 5652 6009 6364 6441 5611 5664 7010 6368 7010 5613 5682 6012 6369 7508 5614 5685 6013 6370 7804 5615 5686 6015 6371 7803 5616 5688 6016 6372 7534 5617 5689 6017 6373 7504 5618 5691 6018 6374 5818 5619 6440 6019 6375 Thanks in advance Deepak |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Look Up Formula Help Needed
If your original table is in columns A thru D in Sheet1 and your
resulting table in columns A thru D in Sheet2 you may try the following formulas in Sheet2. In cell A2: =Sheet1!A2 In cell B2: =IF(ISERROR(VLOOKUP(Sheet1!B2,Sheet1!$A$2:$A$12,1, FALSE)),"----",Sheet1!B2) In cell C2: =IF(ISERROR(VLOOKUP(Sheet1!C2,Sheet1!$A$2:$A$12,1, FALSE)),"----",Sheet1!C2) In cell D2: =IF(ISERROR(VLOOKUP(Sheet1!D2,Sheet1!$A$2:$A$12,1, FALSE)),"----",Sheet1!D2) Copy cells A2:D2 down thru A12:D12 Hope this helps / Lars-Åke On Mon, 29 Dec 2008 13:37:01 -0800, Deepak wrote: The actual file Store R1 R2 R3 9562 5602 5640 8352 8352 8362 5641 6003 7805 5604 9562 6004 8362 5821 5645 6005 5675 5607 5646 6441 6378 5609 5651 7805 5821 5675 5652 6009 6441 7804 5664 6010 7010 5613 5682 6378 7508 5614 7508 6013 7804 5615 7010 6015 The Result should be as below Store R1 R2 R3 9562 ---- ---- 8352 8352 8362 ---- ---- 7805 ---- 9562 ---- 8362 5821 ---- ---- 5675 ---- ---- 6441 6378 ---- ---- 7805 5821 5675 ---- ---- 6441 7804 ---- ---- 7010 ---- ---- 6378 7508 ---- 7508 ---- 7804 ---- 7010 ---- "Shane Devenshire" wrote: Hi, Please restate your question. Show us what the original data looks like. The sample you gave us doesn't match your description of the source data or the results you want. Note that in the sample the first column is called Store and the next four columns are regions. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Deepak" wrote: I Have a sheet with store and Region Store coloum has unique value and each store number will show in only one Reg (Reg1, Reg2, Reg3, Reg4 or Reg5) for eg. If Store 7010 will be under Reg3 - it wont appear in any other Reg. I want to use a formula which willl show what stores comes in what Reg. Store Reg1 Reg2 Reg3 Reg4 9562 5602 5640 6002 6357 8352 5603 5641 6003 6358 7805 5604 5644 6004 6359 8362 5605 5645 6005 6361 5675 5607 5646 6006 6362 6378 5609 5651 6008 6363 5821 5610 5652 6009 6364 6441 5611 5664 7010 6368 7010 5613 5682 6012 6369 7508 5614 5685 6013 6370 7804 5615 5686 6015 6371 7803 5616 5688 6016 6372 7534 5617 5689 6017 6373 7504 5618 5691 6018 6374 5818 5619 6440 6019 6375 Thanks in advance Deepak |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Needed! | Excel Discussion (Misc queries) | |||
Excel formula to copy/paste formula needed please. | Excel Discussion (Misc queries) | |||
Help needed with a formula, please. | Excel Worksheet Functions | |||
FORMULA NEEDED | Excel Discussion (Misc queries) | |||
Sum formula needed | Excel Discussion (Misc queries) |