Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro reqd -transpose column with variable pattern

0

Hi guys  i wonder if you can help me out with this pls.

this  sample below almost worked except the pattern of data is not compley constant  ie some are 11 rows and some are 12.

https://youtu.be/BOQKWt8zAKs

Answer
Discuss

Answers

0
Selected Answer

(001) Please try this formula on your Example 2 tab.

[D7] =INDEX($A$1:$A$20,(ROW()-7)*(COUNTA($1:$1)-1)+COLUMN()-3)

Copy from D7 to D7:H10.

In order to adapt this formula to your other needs please learn how it works. At the core is a simple INDEX function. =INDEX([Range],[Row],[Column]). The range here is A1:A20. I would urge you to replace the reference with a named range which you can make dynamic. Entering this dynamism into this formula just clutters it up.

Since the Range has only a single column, the function's [Column] argument is omitted. Therefore all of(ROW()-7)*(COUNTA($1:$1)-1)+COLUMN()-3 just specifies a row number. ROW()-7 specifies the row in which the first formula resides which is D7, meaning row 7. We need this number to be 0. Therefore, if you paste the formula into D2 instead of D7 this part should read ROW()-2. =Row()-7, pasted into row 7 and copied down will count 0, 1, 2, 3 etc. 0*5, 1*5, 2*5 etx identifies the group from which to retrieve data.

It's almost the same for COLUMN()-3. We need this number to be 1 in column D. =COLUMN() returns the number of the column in which the formula resides. Therefore, if the formula is in column D it returns 4. If we want this number to be 1 we must subtract 3. Therefore COLUMN()-3 when the formula is in column D but Column()-1 if it were in column B.

=Column()-3 returns 1 when entered in column D and counts from 1 up as you copy this formula to the right. That's how the cells A1, A2,A3 etc. are addressed.

In your Example 2 sheet the items in column A are in groups of 5. Therefore the first name is in Cell1 of the Range or (0*5)+1. The next one is in cell (1*5)+1, then (2*5)+1 etc. I figured you would need 5 captions for your 5 columns and that would be 3 captions only if your data would have groups of 3, 12 captions if your data had groups of 12. I inserted the 5 captions in the range D1:H1. Now COUNTA($1:$1) gives me the number of columns plus 1 for A1 which is not a caption. Therefore you find COUNTA($1:$1)-1 in the formula as the multiplicator for the row number. Of course, there are other ways of using the COUNTA() function to get the number of items in the group. Modify the formula as needed.

Discuss

Discussion

hello  and thankyou so much for your detailed answer.  

Part of my problem is im not very experienced with basic code. 

Ther other issue is that there seems to be variations in the data sets,  ie some lots have extra rows, its seems, due to brackets etc,  maybe i should use find and replace to remove those cells with just a bracket.

Again thank you kindly  :) 
megabuff (rep: 2) Apr 22, '20 at 10:58 pm
An answer can't be better than the question to which it replies. you are still learning about your problem. So I suggest you work with what you have until you can rephrase your question completely and then ask another question based on what you will know then.
Variatus (rep: 4889) Apr 23, '20 at 12:18 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login