Pull data horizontally and place vertically 3 by 3

Hi all. I tried to get it done by myself without success. I have a table with duedates payment status and ammount diplayed horizontally. Each block of 3 cells are DUEDATE STATUS AMMOUNT. There are 6 blocks of 3 cells. I need to list vertically those blocks of 3 cells skipping blanks.

I have attached a sample detailed worksheet.

Thanks in advance.

Anyone... please...

Can I get rid of the columns U to AC in the Get_From_Here sheet? These seem to be temporary workings on your part to produce the results that you require. I will need to use two columns at the end of your table which will be simple counts of the number of transactions per record.

Pete

Sure! Thank you!

Ok, but it's getting a bit late here, so I'll carry on with this tomorrow and get back to you.

Pete

Ok! Thank you again!

You need different formulae going the other way.

The sun has just come out, so I'll go and do some gardening and pick this up again in the evening. I had nearly finished it when I packed in last night/this morning, so it shouldn't take too long later on.

Pete

Yep... Don't worry

Still trying... no success... help

Okay, I've finished this off and attached the workbook. As advised, I deleted the columns at the end of the Get_From_Here sheet, and used columns U and V for simple counts - U2 contains this formula:

=COUNTA(SEND[[#This Row],[duedate1]:[ammount6]])/3

which just counts the number of transactions that you have for each order, and I have this formula in V2:

=U2+V1

which just gives a cumulative count of the transactions. Note that V1 contains zero.

In the Extract_here sheet I used this formula in cell A2:

=IF(ROWS($1:1)>MAX(GET_FROM_HERE!$V:$V),"",INDEX(GET_FROM_HERE!$A:$A,MATCH(ROWS($1:1)-1,GET_FROM_HERE!$V:$V)+1))

which brings across the order number from column A of the Get_From_Here sheet for as many times as there are transactions. Then I used this formula in cell B2:

=IF(Table1[[#This Row],[ORDER NUM]]="","",INDEX(SEND[[duedate1]:[ammount6]],MATCH(ROWS($1:1)-1,GET_FROM_HERE!$V:$V),(ROWS($1:1)-1)*3+COLUMNS($B:B)-INDEX(GET_FROM_HERE!$V:$V,MATCH(ROWS($1:1)-1,GET_FROM_HERE!$V:$V))*3))

which can be copied across into C2 and D2. These will bring across the 3 fields for each transaction when copied down.

If you are using a non-English version of Excel, then the formulae should be translated for you automatically when you open the file.

Hope this helps.

Pete

As you have three blank rows at the top of the source sheet, you need to adjust the formula I gave you to this in B8 of the Extract sheet:

=IF(Table1[[#This Row],[ORDER NUM]]="","",INDEX(SEND[[duedate1]:[ammount6]],MATCH(ROWS($7:7)-1,GET_FROM_HERE!$V:$V)-3,(ROWS($7:7)-1)*3+COLUMNS($B:B)-INDEX(GET_FROM_HERE!$V:$V,MATCH(ROWS($7:7)-1,GET_FROM_HERE!$V:$V))*3))

Do the same for the formulae in C8 and D8, then copy down.

Hope this helps.

Pete

P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

As I entry new records the helper columns don't expand right?

That is correct, but you can copy them down much further than you need if you want to. You might want to change the formula in V2 to this:

=IF(U2=0,"",U2+V1)

Hope this helps.

Pete

Yes, he sure does... but I'd like to post or explain myself better there... CS... I dont feel fine asking asking again again for free! ehehe And I'm stucked on this and I need to move on. You can close.