+ Reply to Thread
Results 1 to 20 of 20

List Data from Cells in a single Table Row into a multi-column and multi-row Range.

  1. #1
    Registered User
    Join Date
    09-20-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    I have tried innumerable things to accomplish what I am posting about.

    I need data, from alternating cells, from a single table row to be returned in a multi-column and multi-row range onto a separate Sheet.

    Here is an example of what I need.
    example.xlsx

    I greatly appreciate any help anyone can offer.

    ---------------------------------------------------------------------------

    Detailed Version of Example:
    example_verydetailedexplainations.xlsx
    Last edited by Asaan; 10-02-2012 at 02:46 PM. Reason: Uploaded "detailed" version of example.

  2. #2
    Registered User
    Join Date
    09-20-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    ***bump***

  3. #3
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    Looks like simarui showed you how to use INDEX to build a table.

    Do you want the 'autofill' to also create the blank rows, fill in the Custom Headers, and the data? It looks like it since you have the red rectangles going over the entire column and not just a section, such as B3:B6. Your layout has other complexities, such as the blank columns (E and I), the skipped cells (stated as 'stuff'), and the insertion of blank rows (1, 7, and 13) and repeating headers in rows (2, 8, and 14) that it becomes a fairly complex formula to keep track of all of those 'extras'.

    I would recommend the blank rows and the custom headers be part of your Table2. They would repeat within the table, but would make the draggable equation in 'Sheet that needs autoskip' much easier to design since you would remove some of the extra checks to see if you now need a blank line or to insert the header rows. You could hide those helper columns if you don't want to see them. You would still have to check to skip the 'stuff' data, but at least you reduced some complexity. Basically, some part of desiging a spreadsheet, especially one with formatting, is to use some helper columns and rows to make your other equations more straight forward.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  4. #4
    Registered User
    Join Date
    09-20-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    Ok Pauleyb, I edited the file to work as you suggested.

    Any help you, or anyone else, can provide will be most appreciated.

  5. #5
    Registered User
    Join Date
    09-20-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    ***bump***

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,059

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    looking at your data...you dont actually have a reference to anything beyond table header 6. what is the logic for pulling in the remaining column data...header 1 +5?. if thats the czase, you should say so, rather that having us try and guess what your intentions are?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Registered User
    Join Date
    09-20-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    Quote Originally Posted by FDibbins View Post
    looking at your data...you dont actually have a reference to anything beyond table header 6. what is the logic for pulling in the remaining column data...header 1 +5?. if thats the czase, you should say so, rather that having us try and guess what your intentions are?
    I don't understand what you are asking. All data needed is placed in its proper place within Table2 and every place the data needs to be referenced to is clearly presented in an "outline" format in 'Sheet that needs autofill Skip'!. If you're refering to the fact I do not have formulas, or incorrect ones, in the columns in 'Sheet that needs autofill Skip'! that is because I am asking for help building a formula that I can drag AutoFill down the columns to return the data as I have it presented. Aside from Column A, 'Sheet that needs autofill Skip'! is NOT a Working Sheet, it is a Visual Reference ONLY to what I need to end result to look like.

    Once again, as I have stated on here, as I within the document itself, I need the data from Table2 in 'Sheet with table'! to reference back to 'Sheet that needs autofill Skip'! in the layout it is presented in by dragging AutoFill down the columns as indicated by the red outlines. I'm not sure how I can make it any clearer.

    ::

    Also, where I have written "Table Header 1 Row 1" it simply needs to reference the blank cells in Column:'Table Header 1'! of Table2. I added the text as per request by Pauleyb asked for, as seen above in his posts, in order to make it easier to build a formula. And "stuff" is simply data irrelevant to 'Sheet that needs autofill Skip'! that needs to be skipped over.

  8. #8
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    You did not do as I intended. It is difficult to put into words, so it is difficult for me to explain and for you to understand.

    What I was trying to propose was multiple blank/header columns to keep the equation much simpler. Whenever you want to skip sections or some other break in the flow (of which your table has plenty), typically ROW() and/or COLUMN() are needed.

    As a 'simple' example. Create a workbook with two sheets. Starting in cell Sheet2!A1 type this data in
    Please Login or Register  to view this content.
    where each space implies a separation of cells.
    in Sheet1!A1 type
    Please Login or Register  to view this content.
    then drag it down to A15

    By repeating Header1 and the Blank cells, my formula did not have to add more 'check code' to see if I should be referencing other areas or not. It just flows. If you don't like all of the Header1's and Blank's, then just hide the columns in Sheet2.

    Maybe VBA would be more straightforward, but this is pretty much grunt work.
    Last edited by Pauleyb; 10-01-2012 at 02:31 PM.

  9. #9
    Registered User
    Join Date
    09-20-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    Before You Read:
    If your reply basically says "Use Access" or is going to patronize me in any way than leave, I don't have the patience for any of it, I am simply looking for help, not advice I cannot use. So please respect my wishes and if you have any helpful advice continue with your reply.

    -----

    I was hoping I could avoid any possibility of being patronized and was really hoping someone would simply help me figure out a formula with how I need things done, but what I am trying to build is a recipe book that will be one in the same with a number of other sheets that canonly be built in excel. I know, Access is much more "friendly" for this, but I know next to nothing on how to use Access and we do not own Access so we can't use it anyway. I also know that referencing external documents is possible to, though every attempt to do that has left me with inaccurate calculations or data on multple lines in something like Word need to be read individually read and then returns data in multiple cells and columns.

    The project I am working on has been long running and I know my boss will not approve of spending money on Access if he does not see the benefit of the the entirety of what this document will be able to do for us. It has far more than just these two sheets I am trying to figure out, though they are primarily the backbone of the entire Workbook.

    At the present time I have a working document that has all these recipes laid out on top of one another in an easy to read way, however due to the fact that we cannot print out the "Recipe Book" in a format that is easy to read with the current version of the document I came up with what I have presented to all of you who have read this and/or have tried to help me in this en devour.

    I am sure just from the words Recipe Book that a there will be those of you that will scowl and throw up your arms in disgust that I am trying to do this, but it's all I can work with. So please aid me in any way you can without suggesting different ways to layout my data when it clearly wont read like a "recipe book". I know it won't be easy, but threads such as this one and another one I started on here are all I have left for options.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,059

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    @ asaan, i dont believe any1 is patronising you, just trying to understand your (rather complex and involved) requirements. what may be completely obvious you, being the architect of your file, is not so clear to the rest of us that dont have your intimate knowledge of your requirements. . what both pauleyb and myself are doing is trying to understand what you want, to help you better. getting short with people who are trying their best to help you, could just end up with them withdrawing there help, or perhaps if you dont have patience, then maybe you should look elsewhere?

    Please Login or Register  to view this content.
    yes i understand this. what i was refering to, to use just 1 example...

    on sheet1, name 3, table header 4, b16, you have a value of Data 3Ba. the ONLY cell that contains that value on sheet2 is under table header 9, J4. yet, nowhere in the table in sheet1 is there any mention or reference to table header 9. i just assumed that to get to table header 9, i had to add 5 to table header 4. is this correct or not??

  11. #11
    Registered User
    Join Date
    09-20-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    Quote Originally Posted by FDibbins View Post
    @ asaan, i dont believe any1 is patronising you, just trying to understand your (rather complex and involved) requirements. what may be completely obvious you, being the architect of your file, is not so clear to the rest of us that dont have your intimate knowledge of your requirements. . what both pauleyb and myself are doing is trying to understand what you want, to help you better. getting short with people who are trying their best to help you, could just end up with them withdrawing there help, or perhaps if you dont have patience, then maybe you should look elsewhere?

    Please Login or Register  to view this content.
    yes i understand this. what i was refering to, to use just 1 example...

    on sheet1, name 3, table header 4, b16, you have a value of Data 3Ba. the ONLY cell that contains that value on sheet2 is under table header 9, J4. yet, nowhere in the table in sheet1 is there any mention or reference to table header 9. i just assumed that to get to table header 9, i had to add 5 to table header 4. is this correct or not??
    I am not saying anyone is currently patronizing me, I just know some people would want to after hearing that I want to design a Recipe Book in Excel instead of Access. All forum threads I found of other people asking how to make one in excel only had replies of "Use Access, Use Access, and Use Access"

    And once again I will try to explain to you, the Table Header 4, 5, and 6 in 'Sheet that needs autofill Skip'! are simply for the WORDS, for there will be MANY columns with the same headers (Ingredient, Quantity, and UOM) in 'Sheet with Table'! which does not need to be repeated from EVERY column they are placed in in the table just from the 3 different places, therefore in ALL the places where Table Header 4, 5, and 6 in 'Sheet that needs autofill Skip'! they wil be replaced with "Ingredient", "Quantity", and "UOM"

    Why did I set them to that in the first place? Because I was avoiding saying this was a Recipe Book, and those keywords would give that away and I would then receive replys of "Use Access, Use Access, and Use Access"....

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,059

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    ok heres what i have to begin with. put this in B3, copied down and across to the NAME1 only, so far.

    =INDEX(Table2[#All],MATCH(INDIRECT("A"&ROW()-ROW(A1)-1),Table2[[#All],[Main Header]],0),MATCH(B$2,Table2[#Headers],0))

    also, change the formula in A1 to =IF(B1="Table Header 1 Row 1",'Sheet with Table'!A2,""). im still working on this part, tho, but it will do for now to est name 1

  13. #13
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    Okay, I was wondering where the Access and patronizing comments came from. I thought you were reading too much between the lines or people had sent you emails.

    About Access. It probably is the right tool. And, since many people buy the Office suite, you may have it. But, you have chosen to use Excel, and I have seen people use Excel as a word processor, database, project planner, etc. It is not efficient, but it is flexible enough to do some work.

    I'll be blunt. What you are asking for is a lot of tedious work. My attempt to help simplify the equation was met with the statement that sheet2 is essentially unmodifiable. Could someone come in and give you the equation you look for? Probably, but I would consider it drudgery and not something that is challenging in a fun way. Other people may have different views, but the fact that your question has remained unanswered is a good indicator that people are viewing this as a problem to stay away from.

    Honestly, it would probably take me about an hour to come up with the perfect formula. Not the time I have. But, I do want to help you solve your problem.

    Look at the example I provided. Understand it. It provides the foundational equations you will need to use: Offset, Mod, Int, and Row. You will also likely need If and Column.

    Now, look at the table you want to create. In each of those cells put in the x,y coordinate needed to get that value assuming the origin is A1 on your second sheet. For example, your Sheet1!B1 would be (0,1) since you want 0 rows and 1 column offset from Sheet2!A1. Sheet1!B2 would be (0,4) since you want 0 rows and 4 columns from Sheet2!A2. Last example, Sheet1!B3 would be (1,4) since you want 1 row down and 4 rows over from Sheet2!A1 to get to 'Data 1 Aa'.

    Once that is completed, look at those numbers. Those are the numbers you need to calculate for the OFFSET function. Using other functions, you need to figure out how to calculate those numbers. For example, 'Header 4' repeats every 6 rows starting on row 2. So, that means you want (0,4) every time you are on rows 2, 8, 14, etc. Which is 2+6*n. To check for this in your spreadsheet, you would do something like:
    =IF(MOD(ROW()-1,6)-1=0, OFFSET(Sheet2!A1,0,4), xxxxxxx), where xxxxxxx will be your other equations. Of course, this gets even more complex because you will also need to consider the column you are in, so that you also need to utilize the COLUMN function and bring that into your offset. And that is exactly why your request is so arduous.

    Am I being patronizing? Your call. I view it as trying to teach you how to fish so that you can solve this problem and any future ones on your own.

    Pauley

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,059

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    example for asaan.xlsxnot sure if this is something you can work with, but i have simplified your sheet1 for you. it pulls the data as you want it, all you will need to so is modify the formulas a bit is you add extra "tables for instance beyond the 18 "table headers" and 4 "names" you currently use.

    edit: looks like my file didnt attach the 1st time, lets try again
    Last edited by FDibbins; 10-02-2012 at 02:18 AM.

  15. #15
    Registered User
    Join Date
    09-20-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    Quote Originally Posted by Pauleyb View Post
    Okay, I was wondering where the Access and patronizing comments came from. I thought you were reading too much between the lines or people had sent you emails.

    About Access. It probably is the right tool. And, since many people buy the Office suite, you may have it. But, you have chosen to use Excel, and I have seen people use Excel as a word processor, database, project planner, etc. It is not efficient, but it is flexible enough to do some work.

    I'll be blunt. What you are asking for is a lot of tedious work. My attempt to help simplify the equation was met with the statement that sheet2 is essentially unmodifiable. Could someone come in and give you the equation you look for? Probably, but I would consider it drudgery and not something that is challenging in a fun way. Other people may have different views, but the fact that your question has remained unanswered is a good indicator that people are viewing this as a problem to stay away from.

    Honestly, it would probably take me about an hour to come up with the perfect formula. Not the time I have. But, I do want to help you solve your problem.

    Look at the example I provided. Understand it. It provides the foundational equations you will need to use: Offset, Mod, Int, and Row. You will also likely need If and Column.

    Now, look at the table you want to create. In each of those cells put in the x,y coordinate needed to get that value assuming the origin is A1 on your second sheet. For example, your Sheet1!B1 would be (0,1) since you want 0 rows and 1 column offset from Sheet2!A1. Sheet1!B2 would be (0,4) since you want 0 rows and 4 columns from Sheet2!A2. Last example, Sheet1!B3 would be (1,4) since you want 1 row down and 4 rows over from Sheet2!A1 to get to 'Data 1 Aa'.

    Once that is completed, look at those numbers. Those are the numbers you need to calculate for the OFFSET function. Using other functions, you need to figure out how to calculate those numbers. For example, 'Header 4' repeats every 6 rows starting on row 2. So, that means you want (0,4) every time you are on rows 2, 8, 14, etc. Which is 2+6*n. To check for this in your spreadsheet, you would do something like:
    =IF(MOD(ROW()-1,6)-1=0, OFFSET(Sheet2!A1,0,4), xxxxxxx), where xxxxxxx will be your other equations. Of course, this gets even more complex because you will also need to consider the column you are in, so that you also need to utilize the COLUMN function and bring that into your offset. And that is exactly why your request is so arduous.

    Am I being patronizing? Your call. I view it as trying to teach you how to fish so that you can solve this problem and any future ones on your own.

    Pauley
    From my past searches on Recipe Books in Excel all people got in response was "Use Access".

    And I know it is tedious and complex, that's why I am here. I am hoping to receive help one bit at a time. No I don't expect someone to come along and say "here ya go" with the entire formula, but as I have many other things I do here aside from sit in front of Excel all day (run website, run lab, office tech support, and other odd jobs) I cannot spend an enormous amount of time on this every day and each time I do work on it I have to wrap my brain around it once more each time.

    Also, we do not in fact have Access. My boss saves money by purchasing Microsoft Office Home and Student which only has Word, Excel, OneNote, and Powerpoint. And he will not buy Access because he does not understand the benefit we would have with using it. So I am left to asking for help on here.

    I do understand that you are trying to teach me, however not everyone learns equations or things relative to mathematics from paragraphs of explaination, sometimes people learn much better with an example or two they can work with and wrap their heads around. On that note I will indeed try to work with the equation you just provided. The last one though confused me when I tried implementing it last, but I will give it another try.

    And no you're not being patronizing per say, you're stating the truth that Access would be a better option but you are not saying "leave us alone and give up hope with Excel, go use Access".

    -----------------------------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------------------------

    Quote Originally Posted by FDibbins View Post
    Attachment 184137not sure if this is something you can work with, but i have simplified your sheet1 for you. it pulls the data as you want it, all you will need to so is modify the formulas a bit is you add extra "tables for instance beyond the 18 "table headers" and 4 "names" you currently use.

    edit: looks like my file didnt attach the 1st time, lets try again
    Not to sound like I don't appreciate the effort you are putting forth to trying to help me, because I do appreciate the effort, but it's not helpful and you are going to make me go postal from frustration.

    ONE: The formula in ColumnA in Sheet1 does not need to be altered, I received help with that one in another thread and it was ideal the way it was.

    TWO: Table2 is set up in a way that you cannot alter the layout whatsoever because each ingredient has quantity and UOM, as well as other sensative data that corresponds to each, these were labeled as "stuff" because they DO NOT appear on Sheet2 at all.

    ::

    Here is a very detailed version of what I need with better labeling and showing how everything flows (since now its clear to you all that it's a Recipe Book and there's no way of hiding that now). Hopefully you will understand with this one: example_verydetailedexplainations.xlsx
    Last edited by Asaan; 10-03-2012 at 09:25 AM.

  16. #16
    Registered User
    Join Date
    09-20-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    ***bump***

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,059

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    the restrictions you place on us makes me unable to help you further, sorry

  18. #18
    Registered User
    Join Date
    09-20-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    Not to sound like I am judging every member of this forum, but from what I have seen competence and help are slim pickings; not nonexistent, just hard to find. I do appreciate informative effort that is put forth like Pauleyb has, despite the fact that I still am having trouble.

    Also, I refuse to acknowledge FDibbins as any sort of credible "help" on this forum. Pauleyb, who actually has tried to help without changing the way I have structured things, seems to believe it IS possible; however he does not wish to take the time to build such a formula.

    And if I have crossed some sort of line anywhere in this thread than I apologize, but people seem to give up quickly on here. The fact that this thread has had more than 350 views makes me think people don't want to help unless they can give a helpful answer in about 10 mins. And that frustrates me. I have worked on this workbook for roughly a year and haven't given up, despite the obstacles I have had to overcome.
    Last edited by Asaan; 10-05-2012 at 09:10 AM.

  19. #19
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,059

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    i didnt say it couldnt be done, just that i was not prepared to help further, but im sure with some more diligent work on your part will overcome the other obstacles you face.
    good luck

  20. #20
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: List Data from Cells in a single Table Row into a multi-column and multi-row Range.

    I hesitate to comment on this thread, since I don't want to add salt to Asaan's wound, nor do I wish to appear that I speak for everybody...

    But, I think you are 90% correct Asaan, with your 10 minute statement. Generally, I focus on some low hanging fruit (i.e. 10 minutes or less), difficult problems that may improve my skills, or, every now and then, I look for some issues that have had 0 replies in over 24 hours (as was your case). Many times those 0 reply questions just need some clarification from the author, since people seem to stay away from ill-formed requests. I generally peruse this forum when I have a little break at work, during a boring meeting, or even during lunch; so, yes, I do tend to focus on the types I described above. I believe I am not alone.

    I consider myself to be a straight talker, and I have to admit that I am surprised you haven't been able to resolve this with the example and process I provided. Maybe math is not your thing, or you are still stuck on some trick, but your obvious passion to get this realized made me think you would see this through come hell or high water. As you said, you have spent roughly a year on this, and I assume you really want this layout.

    Here are my suggestions:
    1) Try to work it out with the given examples and process. People are more willing to help with a specific problem when they feel the person is trying to fix an issue themselves and are not being asked to provide an entire solution from scratch.
    2) Post this over in the VBA area of this forum. I have thought about your issue and have mentally debated if a formula or VBA code would be easier. I think VBA would be, as that is how I would ultimately solve this. My advice is to provide your actual spreadsheet as much as possible because any VBA will be custom made for your input layout and requested output layout. You will **** people off if they generate the code to your specifications and then you later come in with "Your code is not working because I forgot to mention that ....".

    <edit>
    Apparently there is some profanity filter for this site. The **** was added by the system. It is a word that begins with p and ends with s. Didn't think that was too vulgar, but, I can't argue with trying to keep this system clean.
    Last edited by Pauleyb; 10-05-2012 at 01:08 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1