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

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

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

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

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