A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Index Match ?



 
 
Thread Tools Display Modes
  #1  
Old May 15th, 2009, 09:10 AM posted to microsoft.public.excel.misc
Rugby Al
external usenet poster
 
Posts: 1
Default Index Match ?

In my worksheet I rows of values as follows in individual cells
ABC 72 30 DEF 67 20 GHI 77 33 JKL 80 12
DEF 77 23 GHI 77 34 ABC 73 31 JKL 81 13
and so on.... The same text will not always be in the same column
What I'm trying to do, is then create a list that will return the values as
follows:
ABC DEF GHI JKL ABC DEF GHI JKL
72 67 77 80 30 20 33 12
73 77 77 81 31 23 34 13
Hope this has made sense

  #2  
Old May 15th, 2009, 05:32 PM posted to microsoft.public.excel.misc
CLR
external usenet poster
 
Posts: 1,638
Default Index Match ?

Please provide a bit more info. I don't really grasp what it is you have to
start with.....is all you data in Columns A, B, and C?


Vaya con Dios,
Chuck, CABGx3



"Rugby Al" wrote:

In my worksheet I rows of values as follows in individual cells
ABC 72 30 DEF 67 20 GHI 77 33 JKL 80 12
DEF 77 23 GHI 77 34 ABC 73 31 JKL 81 13
and so on.... The same text will not always be in the same column
What I'm trying to do, is then create a list that will return the values as
follows:
ABC DEF GHI JKL ABC DEF GHI JKL
72 67 77 80 30 20 33 12
73 77 77 81 31 23 34 13
Hope this has made sense

  #3  
Old May 15th, 2009, 06:06 PM posted to microsoft.public.excel.misc
Rugby Al[_2_]
external usenet poster
 
Posts: 1
Default Index Match ?

Each row contains data which is entered on a daily basis, the text relates to
a location this data is then followed by 2 numeric enteries in the next 2
cells. Below I've comma seperated the data, so each bit of data is in a
single cell.
ABC,72,30,DEF,67,20,GHI,77,33,JKL,80,12
DEF,77,23,GHI,77,34,ABC,73,31,JKL,81,13

The output i'm looking for is to be able to sort the data, so I can list
each locations score on a daily basis which in turn will be used for chart
data. If tried using Index and match, hlookup but keep getting errors.
If I can send an extract oof the data it may help.
Regards

Allan



"CLR" wrote:

Please provide a bit more info. I don't really grasp what it is you have to
start with.....is all you data in Columns A, B, and C?


Vaya con Dios,
Chuck, CABGx3



"Rugby Al" wrote:

In my worksheet I rows of values as follows in individual cells
ABC 72 30 DEF 67 20 GHI 77 33 JKL 80 12
DEF 77 23 GHI 77 34 ABC 73 31 JKL 81 13
and so on.... The same text will not always be in the same column
What I'm trying to do, is then create a list that will return the values as
follows:
ABC DEF GHI JKL ABC DEF GHI JKL
72 67 77 80 30 20 33 12
73 77 77 81 31 23 34 13
Hope this has made sense

  #4  
Old May 15th, 2009, 08:25 PM posted to microsoft.public.excel.misc
CLR
external usenet poster
 
Posts: 1,638
Default Index Match ?

I've run out of day here, but to tell you where I am, I've put ABC in M1 and
DEF in N1, etc.....
I put your rows starting in Rowa2:L2
in M2 I put this and copied it out to p2,
=IF(MATCH(M$1,$A2:$L2)=1,$B2,IF(MATCH(M$1,$A2:$L2) =4,$E2,IF(MATCH(M$1,$A2:$L2)=7,$H2,$K2)))

and in Q2 put this and copied right to T2
=IF(MATCH(Q$1,$A2:$L2)=1,$C2,IF(MATCH(Q$1,$A2:$L2) =4,$F2,IF(MATCH(Q$1,$A2:$L2)=7,$I2,$L2)))

It seemed to work fine except I ran in to trouble when I tried to copy
down....for some reason some columns worked ok and some not.....but anyway, I
thought I would tell you all this to think about......gotta run now, sorry.

Vaya con Dios,
Chuck, CABGx3

"Rugby Al" wrote:

Each row contains data which is entered on a daily basis, the text relates to
a location this data is then followed by 2 numeric enteries in the next 2
cells. Below I've comma seperated the data, so each bit of data is in a
single cell.
ABC,72,30,DEF,67,20,GHI,77,33,JKL,80,12
DEF,77,23,GHI,77,34,ABC,73,31,JKL,81,13

The output i'm looking for is to be able to sort the data, so I can list
each locations score on a daily basis which in turn will be used for chart
data. If tried using Index and match, hlookup but keep getting errors.
If I can send an extract oof the data it may help.
Regards

Allan



"CLR" wrote:

Please provide a bit more info. I don't really grasp what it is you have to
start with.....is all you data in Columns A, B, and C?


Vaya con Dios,
Chuck, CABGx3



"Rugby Al" wrote:

In my worksheet I rows of values as follows in individual cells
ABC 72 30 DEF 67 20 GHI 77 33 JKL 80 12
DEF 77 23 GHI 77 34 ABC 73 31 JKL 81 13
and so on.... The same text will not always be in the same column
What I'm trying to do, is then create a list that will return the values as
follows:
ABC DEF GHI JKL ABC DEF GHI JKL
72 67 77 80 30 20 33 12
73 77 77 81 31 23 34 13
Hope this has made sense

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:48 PM.


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