#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Arrangement Port Man Excel Discussion (Misc queries) 1 March 1st 07 08:33 PM
Arrangement problems. Blah Excel Worksheet Functions 0 April 25th 06 12:21 AM
Arrangement harvindersingh1 Excel Discussion (Misc queries) 2 April 18th 06 07:46 AM
Data Arrangement tbobo Excel Discussion (Misc queries) 3 March 23rd 06 03:50 PM
Re-arrangement William Excel Discussion (Misc queries) 4 October 26th 05 09:11 AM


All times are GMT +1. The time now is 12:20 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"