Data Retrieval: Return values from several columns
Data Retrieval: Return values from several columns
(OP)
All,
Data is listed in rows and columns as outlined below. Column data in assigned row varies from row-row and there may be multiple columns with data. Depending upon selected row (value in row), retrieve only appropriate column data in selected row. There are > 200 rows and 40 columns in database (single sheet).
part a part b part c part d part e ...
list a x x x
list b x x x x
list c x x x
list d x x x x
.
.
.
-when row list c is retrived, only column data part a, d, & e are shown.
-when row a is selected, only column data part a, c, & d are shown.
-etc.
using drop down boxes, array formulas, and/or other standard excel features (please, no or little vba), please provide suggestions, recommendations, or examples to solve this matter.
Thanks!
Data is listed in rows and columns as outlined below. Column data in assigned row varies from row-row and there may be multiple columns with data. Depending upon selected row (value in row), retrieve only appropriate column data in selected row. There are > 200 rows and 40 columns in database (single sheet).
part a part b part c part d part e ...
list a x x x
list b x x x x
list c x x x
list d x x x x
.
.
.
-when row list c is retrived, only column data part a, d, & e are shown.
-when row a is selected, only column data part a, c, & d are shown.
-etc.
using drop down boxes, array formulas, and/or other standard excel features (please, no or little vba), please provide suggestions, recommendations, or examples to solve this matter.
Thanks!





RE: Data Retrieval: Return values from several columns
RE: Data Retrieval: Return values from several columns
good idea! I do frequently use the autofilter feature.
However, I believe further description of desired result is needed.
The intent is to develop a form, such that when the row or selected option is chosen, the form will automatically fill out. The variable is the amount of data to return in columns. So the form is created to allow for varying amounts, with a maximum, of data.
Therefore, using the autofilter is great, now get the returned data into a form. I'm thinking more about switching the row and column data...
I learning more about array formulas, but I have not quite mastered the technique. I'm thinking that whatever row is selected, the array formula (or some other std xl function) returns only the needed columnar data (denoted by an "X").
Thanks for your response and suggestion.
RE: Data Retrieval: Return values from several columns
The only other way that might work would be brute force. An equation in 40 different columns all looking up the same row header but returning the result of the column they are in. Then concatenate all the results, or record a macro that just grabs non-blank (using edit - go to - special - constants) values and pastes them where you want them.
Sort of vague, I know, but then I would use the database.
Good luck.
RE: Data Retrieval: Return values from several columns
I could not come with a "No VBA" solution for your task. But short custom VBA function will do the trick.
Name the range containing (list a, list b ... )as "RowHeader", the data matrix as "DataTable". The following VBA function returns horizontal array with all the data cells upfront. It takes the name of the selected row as an argument and shall be entered as an array formula (ctr-shift-enter) in the horizontal range of the same length as "DataTable".
Public Function DeleteSpaces(RowValue As Range)
Dim origRow As Range, finRow()
Dim i%, j%, irow%
' find row index in the RowHeader
irow = Application.WorksheetFunction.Match(RowValue, Range("RowHeader"), 0)
' select row
Set origRow = Range("DataTable").Offset(irow - 1).Resize(1)
ReDim finRow(1 To origRow.Count)
' move cells with data upfront
For i = 1 To origRow.Count
If Not IsEmpty(origRow(i)) Then
j = j + 1
finRow(j) = origRow(i)
End If
Next i
DeleteSpaces = finRow
End Function
You can find the file at http://yakpol.net/downloads/eng-tips-answer.xls
best regards
RE: Data Retrieval: Return values from several columns
I concur with your assessment regarding the "no vba" solution. I've thought about the solution for sometime, without success. I've been told to use access, so ...
thanks for the simple and effective code. rather than returning the value of the cell, the header (i.e. part a, b, c, d, ...) name for the "x" column is the desired returned value. so, if "x" is checked, then return "part ?", else, nothing: next column.
thanks again.
RE: Data Retrieval: Return values from several columns
Somehow I misunderstood the task...
Simple modifications to the code above will deliver the result.
Name your column header range as "ColumnHeader" and change the code as follows:
Public Function DeleteSpaces(RowValue As Range)
Dim origRow As Range, finRow() As String
Dim i%, j%, irow%
irow = Application.WorksheetFunction.Match(RowValue, Range("RowHeader"), 0)
Set origRow = Range("DataTable").Offset(irow - 1).Resize(1)
ReDim finRow(1 To origRow.Count)
For i = 1 To origRow.Count
If Not IsEmpty(origRow(i)) Then
j = j + 1
finRow(j) = Range("ColumnHeader")(i)
End If
Next i
DeleteSpaces = finRow
End Function
Hope it will work!
RE: Data Retrieval: Return values from several columns
Thanks! Code performs well. Situation resolved using vba code.
RE: Data Retrieval: Return values from several columns
I posted a similar question, in the Database forum (yesterday).
My VB knowledge is next to nil. Would you be able to send me information on the code you wrote or a sample file that has this code? It would be most appreciated.
RE: Data Retrieval: Return values from several columns
A sample was already posted by yakpol.
This is a huge help.
Thank you.
RE: Data Retrieval: Return values from several columns
I see that you got valuable response to your post. I do however want to add another simple solution to this -- see if it meets your needs -- here goes
original list
List part a part b part c part d part e
list a x x x
list b x x x x
list c x x x
list d x x x x
sorted left to right on row_of_List_c in descending order
List part a part d part e part b part c
list a x x x
list b x x x x
list c x x x
list d x x x x
as you can see for row_of_list_c I read Part a, Part d, Part e adjacent to it, and the blank columns have been pushed out. If this works for you, one can then write a macro (if necessary) that as you move to a particular row, either on_demand or automatically the table sorts itself to bring all values of interest next to the selected list row.
HTH
Yogi Anand
ANAND Enterprises
RE: Data Retrieval: Return values from several columns
Thanks for your response. Interesting solution though, but i gather that only the row where the cell highlighter is located will display column headings of designated cells in row.
The question was raised and investigated, but a solution without writing vba is not practical. someone even mentioned to create the application using ms access - not to receptive to that idea.