March 13, 2011 at 5:12 am
Hi, Newbie here so be very gentle. Not even sure I'm in the right place.
I use VLOOKUP and HLOOKUP to extract data from databases in a spreadsheet.
However, one of my databases requires a joint V & H lookup Client names are listed in the left hand column. Across the top are various types of jobs. I need to look down the client names and move across the columns to access the information in the type of job column.
Is there a straightforward way to do ths?
Thanks in advance.
David
March 13, 2011 at 5:18 am
Hard to say since we can't see what you can see. Please read the first article I reference below in my signature block regarding asking for help. It will help you post most of the information we will need to hlp you. Besure to include expected results based on the sample data the article has you post as this also provides us with something to test against.
In return for the extra effort on your part to post all the information we need to really help you, you will get tested code return.
March 14, 2011 at 1:26 am
It's hard to understand what you're really trying to do. You mention VLOOKUP and HLOOKUP which are Excel commands and not SQL commands and you are in a SQL Server forum. If you've moved data from a database into Excel and thus want to manipulate it in Excel, you need to find an Excel forum. If you've moved Excel data into MS Access you're in the wrong place. If you've moved Excel data into a SQL Server database (which means you're looking to use SQL or T-SQL commands), you're in the right place.
All that said, if you are looking for an Excel solution and wanted to extract a cell value from an Excel table with client names on a left hand column and job names on the top row, you could use something like the following: " =VLOOKUP("thisclientname",A1:D4,MATCH("thisjobname",A1:D1,0),FALSE) ". The jobnames may need to be sorted from left to right for this to work; I'm not an excel (nor a SQL) expert really.
If you wanted to do something like this in SQL, you would import your excel spreadsheet into a SQL table (e.g. "client_jobs") so that the left-hand column is called "Client_Name" and have each jobname be an additional set of columns, and you could find information for a particular client/jobname pair by doing the SQL command SELECT thisjobname FROM Client_Jobs where Client_Name='thisclientname'.
Alternatively, you might be trying to figure out how to turn a Excel table like you describe (which is known as crosstab or pivot table) into a SQL table in the form:
clientname1, jobname1, value1
clientname1, jobname2, value2
clientname2, jobname1, value3
etc. This table is in a normalized "database" form more conducive to adding new clients or jobs over time. If so, do a web search about reversing or unpivot-ing a pivot table or crosstab table. There are ways to do it in Excel and in SQL, e.g.
http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/
http://www.excelbanter.com/showthread.php?t=186953
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=3914 (particularly relevant is the second robvolk reply)
etc.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply