Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data arrangement
Hi !
I have a 2 worksheets with data, worksheet 1 col A Col b colC point location value 1 3 a 1 4 a 2 5 b 3 3 b 3 6 a 4 4 c 5 15 d worksheet 2 colA col B point location 1 1 1 2 1 3 1 4 1 5 1 6 2 1 2 2 2 3 2 4 2 5 2 6 2 7 3 1 3 2 3 3 3 4 3 5 3 6 3 7 3 8 3 9 Basically i want to include a third column in worksheet 2, which indicates the position of the value from worksheet 1 . so basically my output should be something like this. worksheet 2 colA col B col c point location Value 1 1 - 1 2 - 1 3 a 1 4 a 1 5 - 1 6 - 2 1 - 2 2 - 2 3 - 2 4 - 2 5 b 2 6 - 2 7 - 3 1 - 3 2 - 3 3 b 3 4 - 3 5 - 3 6 a So the second worksheet is just an expanded version of the first, i would just like to indicate at which position the value is found for each point. My data base is huge, so i was wondering if any formula could be applied instead of entering it manually. Value a is found in the third position for the first point in the first worksheet, so i have put the value a against position 3 for point 1 and so on. Thank you for your help in advance Nitya |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data arrangement
Hi Nitya,
Am Tue, 17 May 2016 04:12:42 -0700 (PDT) schrieb Nitya Satheesh: Basically i want to include a third column in worksheet 2, which indicates the position of the value from worksheet 1 . so basically my output should be something like this. worksheet 2 colA col B col c point location Value 1 1 - 1 2 - 1 3 a 1 4 a 1 5 - 1 6 - 2 1 - 2 2 - 2 3 - 2 4 - 2 5 b 2 6 - 2 7 - 3 1 - 3 2 - 3 3 b 3 4 - 3 5 - 3 6 a in sheet2 cell C2: =IFERROR(INDEX(Sheet1!$C$2:$C$10,MATCH(A2&B2,Sheet 1!$A$2:$A$10&Sheet1!$B$2:$B$10,0)),"-") and insert the array formula with CRTL+Shift+Enter and copy down. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data arrangement
On Tuesday, May 17, 2016 at 5:24:06 PM UTC+5:30, Claus Busch wrote:
Hi Nitya, Am Tue, 17 May 2016 04:12:42 -0700 (PDT) schrieb Nitya Satheesh: Basically i want to include a third column in worksheet 2, which indicates the position of the value from worksheet 1 . so basically my output should be something like this. worksheet 2 colA col B col c point location Value 1 1 - 1 2 - 1 3 a 1 4 a 1 5 - 1 6 - 2 1 - 2 2 - 2 3 - 2 4 - 2 5 b 2 6 - 2 7 - 3 1 - 3 2 - 3 3 b 3 4 - 3 5 - 3 6 a in sheet2 cell C2: =IFERROR(INDEX(Sheet1!$C$2:$C$10,MATCH(A2&B2,Sheet 1!$A$2:$A$10&Sheet1!$B$2:$B$10,0)),"-") and insert the array formula with CRTL+Shift+Enter and copy down. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Hi Claus, Thanks for the reply, but It's showing me '-' in all the cells. it is not showing me any values in the correct positions. is there anything i have to change? Nitya |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data arrangement
Hi Nitya,
Am Thu, 19 May 2016 00:10:56 -0700 (PDT) schrieb Nitya Satheesh: Thanks for the reply, but It's showing me '-' in all the cells. it is not showing me any values in the correct positions. is there anything i have to change? it is an array formula. Did you enter it with CTRL+Shift+Enter? Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data arrangement
On Thursday, May 19, 2016 at 12:44:06 PM UTC+5:30, Claus Busch wrote:
Hi Nitya, Am Thu, 19 May 2016 00:10:56 -0700 (PDT) schrieb Nitya Satheesh: Thanks for the reply, but It's showing me '-' in all the cells. it is not showing me any values in the correct positions. is there anything i have to change? it is an array formula. Did you enter it with CTRL+Shift+Enter? Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Hi claus, Thanks! I just tried that and it worked! Nitya |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Arrangement | Excel Discussion (Misc queries) | |||
Arrangement problems. | Excel Worksheet Functions | |||
Arrangement | Excel Discussion (Misc queries) | |||
Data Arrangement | Excel Discussion (Misc queries) | |||
Re-arrangement | Excel Discussion (Misc queries) |