×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Data Retrieval: Return values from several columns

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!

RE: Data Retrieval: Return values from several columns

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.


RE: Data Retrieval: Return values from several columns

(OP)
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").
Thanks for your response and suggestion.

RE: Data Retrieval: Return values from several columns

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.

RE: Data Retrieval: Return values from several columns

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

RE: Data Retrieval: Return values from several columns

(OP)
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.

RE: Data Retrieval: Return values from several columns

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!

RE: Data Retrieval: Return values from several columns

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

RE: Data Retrieval: Return values from several columns

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.

RE: Data Retrieval: Return values from several columns

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

RE: Data Retrieval: Return values from several columns

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


RE: Data Retrieval: Return values from several columns

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close