Transposing Rows to Columns using the Index function

bearcub

Well-known Member
Joined
May 18, 2005
Messages
625
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
I periodically have to upload a file into our commission system where I have to transpose an employee ID in one columns, multiple rows into 1 row multiple columns. I also have to insert 100 between each transposed ID number.

Today, I have 19 people that I have add to the template - 19 rows x 2 columns into 1 row by 38 columns

I have seen this trick on ExcelIsFun but forgot which video it was so I can manipulate the formula. He as several videos on this but I can't remember which one applies to my situation.

Can you transpose something like this using some of the new array functions?

How do I transpose a 19 row, 2 column range to 38 columns (I.d. Employee ID, 100)

Thank you for your help

Michael
 

Attachments

  • Conmission pload.PNG
    Conmission pload.PNG
    44.8 KB · Views: 3

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,775
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff v2.xlsm
ABCDEFGHIJKLMNO
1IDPaidRows
2CA11 0SAYes5
3DY14 0JBYesCA11 0SAYesDY14 0JBYesOL14 6XBYesDL13 1JZYesNE47 9LQYes
4OL14 6XBYes
5DL13 1JZYes
6NE47 9LQYes
7
8
Main
Cell Formulas
RangeFormula
C2C2=COUNTA(A:A)-1
E3:N3E3=INDEX(A2:B100,INT(SEQUENCE(,C2*2,1,0.5)),MOD(SEQUENCE(,C2*2,2),2)+1)
Dynamic array formulas.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,058
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
an alternative available to you is Power Query also called Get & Transform and found on the Data Tab of your Excel Version
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source) 
in
    #"Transposed Table"

Using Fluff's example, here is an alternative presentation.

Book3
ABCDE
1Column1Column2Column3Column4Column5
2CA11 0SADY14 0JBOL14 6XBDL13 1JZNE47 9LQ
3YesYesYesYesYes
Sheet2
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,775
Office Version
  1. 365
Platform
  1. Windows
If your just pasting data into col A, you can automatically add100 to col B like
+Fluff v2.xlsm
ABCDEFGHIJKLMN
1IDPaidRows
2CA11 0SA1005
3DY14 0JB100CA11 0SA100DY14 0JB100OL14 6XB100DL13 1JZ100NE47 9LQ100
4OL14 6XB100
5DL13 1JZ100
6NE47 9LQ100
7
Main
Cell Formulas
RangeFormula
B2:B6B2=SEQUENCE(C2,,100,0)
C2C2=COUNTA(A:A)-1
E3:N3E3=INDEX(A2:B100,INT(SEQUENCE(,C2*2,1,0.5)),MOD(SEQUENCE(,C2*2,2),2)+1)
Dynamic array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,262
Messages
5,595,161
Members
413,972
Latest member
emiguy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top