Data Retrieval: Return values from several columns 1

[pmover]

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!

 

[ivymike]

how 'bout if you transpose the sheet, so that the rows are now columns, and columns are now rows, then select the column labels and hit "auto filter?" That will add those filter pull-down thingies, which you can then use to show only the rows that contain "x" in a particular column.

 

[pmover]

ivymike,

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&quot.
Thanks for your response and suggestion.

 

[Terzaghi]

I know this is a spread sheet forum, but I really think you need to use a data base to efficiently do what you want. Once the data is in a table, you can run a query and select all of the non-null values in any given record (row in excel lingo). Access is a pretty easy database to work with and it will build queries for you if you aren't familiar with SQL.

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.

 

[yakpol]

pmover,
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

 

[pmover]

yakpol,
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.

 

[yakpol]

pmover,
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!

 

[pmover]

yakpol,
Thanks! Code performs well. Situation resolved using vba code.

 

[jelenko]

pmover,

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.

 

[jelenko]

My apologies.
A sample was already posted by yakpol.
This is a huge help.
Thank you.

 

[yogia]

Hi pmover:
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

 

[pmover]

yogia,

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.