Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a way to do a "lookup" and change the orientation of the
I have a file that I use the VLookup function to find the data from other
files. The worksheet is set up with Lables in column "A" and the "names of managers" across Row 1 - starting in Cell A2. Some of my data files are set up with the "Names of the Managers" listed in column A and then the data in column B. Is there an easy way to do a Lookup and transpose the information. Or is there a way to do a Paste Link and Transpose at the same time? Thanks, -- Perry K |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a way to do a "lookup" and change the orientation of the
On Jun 20, 5:57*pm, PerryK wrote:
I have a file that I use the VLookup function to find the data from other files. The worksheet is set up with Lables in column "A" and the "names of managers" across Row 1 - starting in Cell A2. Some of my data files are set up with the "Names of the Managers" listed in column A and then the data in column B. Is there an easy way to do a Lookup and transpose the information. Or is there a way to do a Paste Link and Transpose at the same time? Thanks, -- Perry K Instead of transposing the whole data, why not use a combination of vlookup and hlookup? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a way to do a "lookup" and change the orientation of the
Probably you can do your job just using HLOOKUP instead of VLOOKUP.
HLOOKUP assumes an orientation that is typically transposed. Third argument signifies row rather than column. HTH Kostis Vezerides On Jun 20, 3:57 pm, PerryK wrote: I have a file that I use the VLookup function to find the data from other files. The worksheet is set up with Lables in column "A" and the "names of managers" across Row 1 - starting in Cell A2. Some of my data files are set up with the "Names of the Managers" listed in column A and then the data in column B. Is there an easy way to do a Lookup and transpose the information. Or is there a way to do a Paste Link and Transpose at the same time? Thanks, -- Perry K |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a way to do a "lookup" and change the orientation of
The problem is that one of the data files is oriented to use VLookup and the
other is oriented to use HLookup. I Need to figure out how to get the data from the table that is oriented for VLookup into a table that could be used for HLookup. Thanks -- Perry K "vezerid" wrote: Probably you can do your job just using HLOOKUP instead of VLOOKUP. HLOOKUP assumes an orientation that is typically transposed. Third argument signifies row rather than column. HTH Kostis Vezerides On Jun 20, 3:57 pm, PerryK wrote: I have a file that I use the VLookup function to find the data from other files. The worksheet is set up with Lables in column "A" and the "names of managers" across Row 1 - starting in Cell A2. Some of my data files are set up with the "Names of the Managers" listed in column A and then the data in column B. Is there an easy way to do a Lookup and transpose the information. Or is there a way to do a Paste Link and Transpose at the same time? Thanks, -- Perry K |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a way to do a "lookup" and change the orientation of
Thinking...
Let us assume that you know a trait of the tables that allows you to detect whether it has a vertical or horizontal orientation. E.g. you know the label of the second column/row. Then you could have something like =IF(A2="label",VLOOKUP(...),HLOOKUP(...)) But the question is how you use these lookup functions. If you have several such tables in several spreadsheets, how do you decide on which sheet to look in the first place? Are you using INDIRECT? Also, how do you specify the column (or row for HLOOKUP)? You might have a central table where in the first column you have the sheet name and table area and in the second column you have a V or H to indicate the orientation. Say this range is called tables then you might have something like: =IF(VLOOKUP(F2,tables,2,0)="V",VLOOKUP(K2,INDIRECT (VLOOKUP(F2,tables, 1,0)),col,0),HLOOKUP(K2,INDIRECT(VLOOKUP(F2,tables ,1,0)),row,0)) In either case who decides col and row? HTH Kostis On Jun 20, 4:31 pm, PerryK wrote: The problem is that one of the data files is oriented to use VLookup and the other is oriented to use HLookup. I Need to figure out how to get the data from the table that is oriented for VLookup into a table that could be used for HLookup. Thanks -- Perry K "vezerid" wrote: Probably you can do your job just using HLOOKUP instead of VLOOKUP. HLOOKUP assumes an orientation that is typically transposed. Third argument signifies row rather than column. HTH Kostis Vezerides On Jun 20, 3:57 pm, PerryK wrote: I have a file that I use the VLookup function to find the data from other files. The worksheet is set up with Lables in column "A" and the "names of managers" across Row 1 - starting in Cell A2. Some of my data files are set up with the "Names of the Managers" listed in column A and then the data in column B. Is there an easy way to do a Lookup and transpose the information. Or is there a way to do a Paste Link and Transpose at the same time? Thanks, -- Perry K |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a way to do a "lookup" and change the orientation of
Thanks for the response, but I think I may not have explained the problem
clearly. The Final Report looks like this: Bob Fred John Tim Sales Dollars $50 $100 $250 $175 Sales Qty 2 5 7 9 I use the HLookup function to find the Sales Dollars in a Table that looks like this: Bob Fred John Tim Sales Dollars $50 $100 $250 $175 This works with out any problems. The problem is that the Sales Qty data is in a Table that is oriented like this: Sales Qty Bob 2 Fred 5 John 7 Tim 9 What I am trying to do is get the Sales Qty into the "Report" aligned with the correct Names. Currently I have to copy the Sales Qty data and then Paste Special Transpose. Then I use that table for the HLookup. I have a large qty of data in these reports and it is time consuming to do it this way. That is why I am trying to see if there is an easier way to do it. -- Perry K "vezerid" wrote: Thinking... Let us assume that you know a trait of the tables that allows you to detect whether it has a vertical or horizontal orientation. E.g. you know the label of the second column/row. Then you could have something like =IF(A2="label",VLOOKUP(...),HLOOKUP(...)) But the question is how you use these lookup functions. If you have several such tables in several spreadsheets, how do you decide on which sheet to look in the first place? Are you using INDIRECT? Also, how do you specify the column (or row for HLOOKUP)? You might have a central table where in the first column you have the sheet name and table area and in the second column you have a V or H to indicate the orientation. Say this range is called tables then you might have something like: =IF(VLOOKUP(F2,tables,2,0)="V",VLOOKUP(K2,INDIRECT (VLOOKUP(F2,tables, 1,0)),col,0),HLOOKUP(K2,INDIRECT(VLOOKUP(F2,tables ,1,0)),row,0)) In either case who decides col and row? HTH Kostis On Jun 20, 4:31 pm, PerryK wrote: The problem is that one of the data files is oriented to use VLookup and the other is oriented to use HLookup. I Need to figure out how to get the data from the table that is oriented for VLookup into a table that could be used for HLookup. Thanks -- Perry K "vezerid" wrote: Probably you can do your job just using HLOOKUP instead of VLOOKUP. HLOOKUP assumes an orientation that is typically transposed. Third argument signifies row rather than column. HTH Kostis Vezerides On Jun 20, 3:57 pm, PerryK wrote: I have a file that I use the VLookup function to find the data from other files. The worksheet is set up with Lables in column "A" and the "names of managers" across Row 1 - starting in Cell A2. Some of my data files are set up with the "Names of the Managers" listed in column A and then the data in column B. Is there an easy way to do a Lookup and transpose the information. Or is there a way to do a Paste Link and Transpose at the same time? Thanks, -- Perry K |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a way to do a "lookup" and change the orientation of
Perry,
from what I see you did explain your situation clearly and my remarks were towards this direction. If a table has vertical orientation and you still want to use HLOOKUP you can do it with an *array* formula (commit with Shift+Ctrl+Enter): =HLOOKUP(A2,TRANSPOSE(table),2,0) But why make your life harder? You can also use (no array-entering) =VLOOKUP(A2,table,2,0) Maybe you would like to send me part of your file; if I see the actual conditions I might be able to suggest a functional solution. At any rate, the suggestions of my previous post are still valid after your last reply. My email is: vezerid at act dot edu Regards Kostis On Jun 20, 6:19 pm, PerryK wrote: Thanks for the response, but I think I may not have explained the problem clearly. The Final Report looks like this: Bob Fred John Tim Sales Dollars $50 $100 $250 $175 Sales Qty 2 5 7 9 I use the HLookup function to find the Sales Dollars in a Table that looks like this: Bob Fred John Tim Sales Dollars $50 $100 $250 $175 This works with out any problems. The problem is that the Sales Qty data is in a Table that is oriented like this: Sales Qty Bob 2 Fred 5 John 7 Tim 9 What I am trying to do is get the Sales Qty into the "Report" aligned with the correct Names. Currently I have to copy the Sales Qty data and then Paste Special Transpose. Then I use that table for the HLookup. I have a large qty of data in these reports and it is time consuming to do it this way. That is why I am trying to see if there is an easier way to do it. -- Perry K "vezerid" wrote: Thinking... Let us assume that you know a trait of the tables that allows you to detect whether it has a vertical or horizontal orientation. E.g. you know the label of the second column/row. Then you could have something like =IF(A2="label",VLOOKUP(...),HLOOKUP(...)) But the question is how you use these lookup functions. If you have several such tables in several spreadsheets, how do you decide on which sheet to look in the first place? Are you using INDIRECT? Also, how do you specify the column (or row for HLOOKUP)? You might have a central table where in the first column you have the sheet name and table area and in the second column you have a V or H to indicate the orientation. Say this range is called tables then you might have something like: =IF(VLOOKUP(F2,tables,2,0)="V",VLOOKUP(K2,INDIRECT (VLOOKUP(F2,tables, 1,0)),col,0),HLOOKUP(K2,INDIRECT(VLOOKUP(F2,tables ,1,0)),row,0)) In either case who decides col and row? HTH Kostis On Jun 20, 4:31 pm, PerryK wrote: The problem is that one of the data files is oriented to use VLookup and the other is oriented to use HLookup. I Need to figure out how to get the data from the table that is oriented for VLookup into a table that could be used for HLookup. Thanks -- Perry K "vezerid" wrote: Probably you can do your job just using HLOOKUP instead of VLOOKUP. HLOOKUP assumes an orientation that is typically transposed. Third argument signifies row rather than column. HTH Kostis Vezerides On Jun 20, 3:57 pm, PerryK wrote: I have a file that I use the VLookup function to find the data from other files. The worksheet is set up with Lables in column "A" and the "names of managers" across Row 1 - starting in Cell A2. Some of my data files are set up with the "Names of the Managers" listed in column A and then the data in column B. Is there an easy way to do a Lookup and transpose the information. Or is there a way to do a Paste Link and Transpose at the same time? Thanks, -- Perry K |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I auto transform sets of data? (change "female" to "f") | Excel Discussion (Misc queries) | |||
Pagesetup.Orientation need VBA routine to "Best Guess" Orientation | Excel Discussion (Misc queries) | |||
How do I change the column heading in Excel to display "A" "B" "C | New Users to Excel | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
=IF(D13="PAID","YES","NO") Can I change fonts colour | Excel Discussion (Misc queries) |