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

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.

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?

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.

@ 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??

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

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

Originally Posted by Pauleyb

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".

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

Originally Posted by FDibbins

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

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

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.

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

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.