Hi, look at the spreasheet attached. there is info on column A, and on columns B to F, the values. the idea is to get as result as the columns J and K.
please let me know if someone knows how to solve it using functions.
thx.
Hi, look at the spreasheet attached. there is info on column A, and on columns B to F, the values. the idea is to get as result as the columns J and K.
please let me know if someone knows how to solve it using functions.
thx.
In your sample, there were always 5 horizontal values that needed to be transposed. This will work if there are missing values:
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$6)/($B$2:$F$6<>""),ROWS(M$2:M2)),1),"")
and
=IF($M2="","",INDEX(B$2:F$6,MATCH($M2,$A$2:$A$6,0),(COUNTIF($M$2:$M2,$M2))))
If there are ALWAYS 5 and ONLY 5 it can be simplified a bit...
Please check your profile to ensure that the Excel version you're using is still correct. Change it, as required.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi brjohnsmith,
See if this formula doesn't solve your problem.
Formula:Please Login or Register to view this content.
Array to 2 Columns.xlsx
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
...another option
In A7 =INDEX($A$2:$A$6,MOD(ROW()-2,5)+1,1)
In B7 =INDEX($B$2:$F$6,INT((ROW()-6.5)/5+1),MOD(ROW()-2,5)+1)
Copy A7:B7 down for 25 rows
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
Hi Glenn, thank you, it worked , this was only an example, it could have more columns and lines. I adjusted it. however, I had to pull down the values until to the max combination, is there a way to set the complete values at once? thx.
The following isn't fully automatic; however, it will automatically adjust when rows are added/deleted.
1. Add column headers in A1:F1
2. Convert the range A1:F6 into an Excel table
3. Use the following modification of Glenn's formula for column M:Formula:Please Login or Register to view this content.
4. Use the following modification of Glenn's formula for column N:Formula:Please Login or Register to view this content.
Note that if columns are added then the [Column6] references will need to be changed.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks