Transposing a row with a difference

Maij · Oct 1, 2011

Hi all. Some really sharp experts in here. I hope by joining Mr Excel, I will reach a good standard. AS YOU GUYS.

I hit a BRICK wall trying to figure out this problem. I have a worksheet with several columns and hundreds of rows. Each row contains information about a football match such as date , Match Id , players Names and so on. The main worksheet for that contains 56 columns.
What I wanted to do by using VBA is to
1) loop through all rows
2) Select a range "D" to "N" , copy that range and then paste special transpose on Destination worksheet. That would form 11 new rows
3) On the original worksheet there are two columns let us say column "A" and Column "C" that contains data (match_id and Team_ID)
4) I need the to paste those to cells in the same 11 rows of the destination worksheet.

Here is the example

ORIGINAL worksheet

.......A - B -C - D- E -F- G- H -I -J -K- L- M- N
1-2435- 108 , john - jack- Fred- Peter- Hays - Hall-Foster- Clark- Gable -, Johns- Meyers
2-2431-Feb,89- 77 , Harry , Johann , Roberto , Craig ....etc
3-
4-
5-
6-
7-
8-
9-
10-
11-

DESTINATION Worksheet

.....A....B....C....
1 -2435- 108-john
2 -2435- 108- jack
3 -2435- 108- Fred
4 -2435- 108 -Peter
5 -2435- 108 - Hays
6 -2435- 108 - Hall
7 -2435- 108 - Foster
8 -2435- 108 - Clark
9 -2435- 108 - Gable
10-2435- 108 - Johns
11-2435- 108 - Meyers
12-2431-77 - Harry
13-2431-77- Johann
14-2431-77- Roberto
15-2431-77- Craig
16....etc

I Have tried several codes but none worked perfectly as I wanted. I do appreciate any help in resolving this.

hiker95 · Oct 1, 2011

Maij,

Welcome to the MrExcel forum.

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (sensitive data scrubbed/removed/changed) (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

Maij · Oct 1, 2011

Thanks for your response Hiker 95.

I have gone through at least 10 sets of coding and this is the last one. This one is probbaly the worst , cuz it just gives me an error on SpecialPaste (transpose).

I have commented on a few lines for debugging purpose.

Here is the code.

Public Sub CopyRows1()
Sheets("Sheet4").Select
' Find the last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
' Loop through each row
For x = 1 To FinalRow

Match_ID = Cells(x, 1).Value
MsgBox ("Match_ID =" & Match_ID) ' debugging purpose only'
Team_ID = Cells(x, 3).Value
MsgBox ("Team_ID =" & Team_ID) ' debugging purpose only'

Cells(x, 4).Resize(1, 11).Copy
Sheets("Sheet3").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 3).Select
'Cells(NextRow, 1).Value = Match_ID
'Cells(NextRow, 2).Value = Team_ID
' Cells(NextRow, 3).Select

Selection.PasteSpecial , Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Sheets("Sheet4").Select

Next x
End Sub

hiker95 · Oct 1, 2011

Maij,

I would like to see what the raw data actually looks like.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, mark the workbook for sharing, and provide us with a link to your workbook.

Maij · Oct 1, 2011

OK , I have posted a screen shoot of the worksheet HERE

The image title is scap1.jpg
Thanks

hiker95 · Oct 1, 2011

Maij,

You are posting a picture. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense and I doubt that you would get any answer.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, mark the workbook for sharing, and provide us with a link to your workbook.

If you are not able to give us screenshots with one of the utilities/addins that are below in my Signature block, and if you can not post your workbook to Box.net, then:

Click on the Post Reply button, then just enter the word BUMP, and click on the Submit Reply button, and someone else will assist you.

Maij · Oct 1, 2011

Thanks for your time Hiker95

It is approaching 3 AM where I live , and the brain is not fully functional. All the suggested tools that you have mentioned in order for me to post the code/spreadsheet has a learning curve and/or some restrictions.

I have to leave it for now and hit the sack , to start fresh next morning.

Appreciate your time.

Maij · Oct 2, 2011

OK , Here is the worksheet , or part of it

<table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 99px"> <col style="WIDTH: 99px"> <col style="WIDTH: 99px"> <col style="WIDTH: 99px"> <col style="WIDTH: 99px"> <col style="WIDTH: 99px"> <col style="WIDTH: 99px"> <col style="WIDTH: 99px"> <col style="WIDTH: 99px"> <col style="WIDTH: 99px"> <col style="WIDTH: 99px"> <col style="WIDTH: 99px"> <col style="WIDTH: 99px"> <col style="WIDTH: 99px"> <col style="WIDTH: 99px"></colgroup> <tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td> <td>L</td> <td>M</td> <td>N</td> <td>O</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</td> <td style="TEXT-ALIGN: right">2443</td> <td style="TEXT-ALIGN: right">28-Dec-09</td> <td style="TEXT-ALIGN: center">6</td> <td>Qassim BORHAN</td> <td>Hamid Ismaeel KHALIFA</td> <td>Khalid Saleh AL ZAKIBA</td> <td>Mohammed AbdulRab AL YAZIDI</td> <td>Fabio CESAR</td> <td>Majid Imam HASSAN</td> <td>Ibrahim MAJID</td> <td>Talal AL BLOUSHI</td> <td>Ahmed Faris AL BINALI</td> <td>Ali Hassan AFIF</td> <td>Bilal Mohammed RAJAB</td> <td>
</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</td> <td style="TEXT-ALIGN: right">2442</td> <td style="TEXT-ALIGN: right">2-Jan-10</td> <td style="TEXT-ALIGN: center">59</td> <td>Jun Kwang Min</td> <td>Ryang Mong Il</td> <td>Kim Myong Gyu</td> <td>Chae Tu Yong</td> <td>Pak Song Chol</td> <td>Choe Chol Man</td> <td>Sing Yong Nam</td> <td>Jon Kwak Ik</td> <td>Yun Yong Il</td> <td>Pak Yong Jin</td> <td>Pak Nam Chol</td> <td>
</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</td> <td style="TEXT-ALIGN: right">2441</td> <td style="TEXT-ALIGN: right">30-Dec-09</td> <td style="TEXT-ALIGN: center">104</td> <td>Mahamadou Sidibé</td> <td>Souleymane Diamouténé</td> <td>Bakary Soumaré</td> <td>Amadou Sidibé</td> <td>Drissa Diakité</td> <td>Mamadou Bagayoko</td> <td>Lassana Fané</td> <td>Mahamadou Diarra</td> <td>Moustapha Yattabaré</td> <td>Tènèma NDiaye</td> <td>Mamadou Samassa</td> <td>
</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</td> <td style="TEXT-ALIGN: right">2439</td> <td style="TEXT-ALIGN: right">18-Nov-09</td> <td style="TEXT-ALIGN: center">64</td> <td>Tome Pacovski </td> <td>Goce Sedloski</td> <td>Goran Popov</td> <td>Nikolce Noveski</td> <td>Slavco Georgievski</td> <td>Dusan Savi</td> <td>Velice Sumulikoski</td> <td>Filip Ivanovski</td> <td>Filip Despotovski</td> <td>Robert Popov</td> <td>Goran Pandev</td> <td>
</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</td> <td style="TEXT-ALIGN: right">2438</td> <td style="TEXT-ALIGN: right">5-Sep-09</td> <td style="TEXT-ALIGN: center">96</td> <td>Ignatiy Nesterov</td> <td>Aziz Ibrahimov</td> <td>Stanislav Andreev</td> <td>Azizbek Haydarov</td> <td>Wagner Polodin</td> <td>Anzur Ismailov</td> <td>Anvar Gafurov</td> <td>Server Djeparov</td> <td>Aleksandr Geynrikh</td> <td>Odil Ahmedov</td> <td>Farhod Tadjiyev</td> <td>
</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</td> <td style="TEXT-ALIGN: right">2437</td> <td style="TEXT-ALIGN: right">31-Aug-09</td> <td style="TEXT-ALIGN: center">15</td> <td>Abbas AHMED</td> <td>Mohammed HUBAIL</td> <td>Hussain BABA</td> <td>Ebrahim AL MISHKHAS</td> <td>Salman ISSA</td> <td>Ahmed HAS'SAN</td> <td>Mohammed SALMIN</td> <td>Abdulwahab ALI</td> <td>Mahmood ABDUL RAHMAN</td> <td>Ismael ABDUL LATIF</td> <td>Hussain ALI</td> <td>
</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</td> <td style="TEXT-ALIGN: right">2435</td> <td style="TEXT-ALIGN: right">22-Nov-09</td> <td style="TEXT-ALIGN: center">10</td> <td>Amer Shafi Mahmoud </td> <td>Hatem Aqal</td> <td>Hassouneh Al Sheikh</td> <td>Khalid Abdullah</td> <td>Abdulla Deeb</td> <td>Bassam Fathi</td> <td>Mahmood Jamal</td> <td>Amer Deeb Mohammed</td> <td>Baha' Abdelrahman Mustafa</td> <td>Anas Waleed Bani-Yassin</td> <td>Mohammed Abdul Halim</td> <td>
</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</td> <td style="TEXT-ALIGN: right">2434</td> <td style="TEXT-ALIGN: right">14-Nov-09</td> <td style="TEXT-ALIGN: center">10</td> <td>Amer Shafi Mahmoud </td> <td>Hatem Aqal</td> <td>Mahmoud Shilbaye</td> <td>Hassouneh Al Sheikh</td> <td>Khalid Abdullah</td> <td>Abdulla Deeb</td> <td>Bassam Fathi</td> <td>M. Jamal</td> <td>Amer Deeb Mohammed</td> <td>Baha' Abdelrahman Mustafa</td> <td>Anas Waleed Bani-Yassin</td> <td>
</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">33</td> <td style="TEXT-ALIGN: right">2433</td> <td style="TEXT-ALIGN: right">17-Jun-09</td> <td style="TEXT-ALIGN: center">8</td> <td>LEE Woon Jae</td> <td>OH Beom Seok</td> <td>CHO Yong Hyung</td> <td>PARK Ji Sung</td> <td>KIM Jung Woo</td> <td>PARK Chu Young </td> <td>LEE Keun Ho</td> <td>LEE Young Pyo</td> <td>LEE Jung Soo</td> <td>KI Sung Yueng</td> <td>LEE Chung Yong</td> <td>
</td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

Maij · Oct 2, 2011

And here what I want the result to be at Destination sheet.

The table here shows TWO transposed cells from the original worksheet , ROW 25 & 26

<table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 99px"> <col style="WIDTH: 99px"> <col style="WIDTH: 214px"></colgroup> <tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td>
</td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">593</td> <td style="TEXT-ALIGN: right">2443</td> <td style="TEXT-ALIGN: center">6</td> <td>Qassim BORHAN</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">594</td> <td style="TEXT-ALIGN: right">2443</td> <td style="TEXT-ALIGN: center">6</td> <td>Hamid Ismaeel KHALIFA</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">595</td> <td style="TEXT-ALIGN: right">2443</td> <td style="TEXT-ALIGN: center">6</td> <td>Khalid Saleh AL ZAKIBA</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">596</td> <td style="TEXT-ALIGN: right">2443</td> <td style="TEXT-ALIGN: center">6</td> <td>Mohammed AbdulRab AL YAZIDI</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">597</td> <td style="TEXT-ALIGN: right">2443</td> <td style="TEXT-ALIGN: center">6</td> <td>Fabio CESAR</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">598</td> <td style="TEXT-ALIGN: right">2443</td> <td style="TEXT-ALIGN: center">6</td> <td>Majid Imam HASSAN</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">599</td> <td style="TEXT-ALIGN: right">2443</td> <td style="TEXT-ALIGN: center">6</td> <td>Ibrahim MAJID</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">600</td> <td style="TEXT-ALIGN: right">2443</td> <td style="TEXT-ALIGN: center">6</td> <td>Talal AL BLOUSHI</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">601</td> <td style="TEXT-ALIGN: right">2443</td> <td style="TEXT-ALIGN: center">6</td> <td>Ahmed Faris AL BINALI</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">602</td> <td style="TEXT-ALIGN: right">2443</td> <td style="TEXT-ALIGN: center">6</td> <td>Ali Hassan AFIF</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">603</td> <td style="TEXT-ALIGN: right">2443</td> <td style="TEXT-ALIGN: center">6</td> <td>Bilal Mohammed RAJAB</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">604</td> <td style="TEXT-ALIGN: right">2442</td> <td style="TEXT-ALIGN: center">59</td> <td>Jun Kwang Min</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">605</td> <td style="TEXT-ALIGN: right">2444</td> <td style="TEXT-ALIGN: center">59</td> <td>Ryang Mong Il</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">606</td> <td style="TEXT-ALIGN: right">2444</td> <td style="TEXT-ALIGN: center">59</td> <td>Kim Myong Gyu</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">607</td> <td style="TEXT-ALIGN: right">2444</td> <td style="TEXT-ALIGN: center">59</td> <td>Chae Tu Yong</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">608</td> <td style="TEXT-ALIGN: right">2444</td> <td style="TEXT-ALIGN: center">59</td> <td>Pak Song Chol</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">609</td> <td style="TEXT-ALIGN: right">2444</td> <td style="TEXT-ALIGN: center">59</td> <td>Choe Chol Man</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">610</td> <td style="TEXT-ALIGN: right">2444</td> <td style="TEXT-ALIGN: center">59</td> <td>Sing Yong Nam</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">611</td> <td style="TEXT-ALIGN: right">2444</td> <td style="TEXT-ALIGN: center">59</td> <td>Jon Kwak Ik</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">612</td> <td style="TEXT-ALIGN: right">2444</td> <td style="TEXT-ALIGN: center">59</td> <td>Yun Yong Il</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">613</td> <td style="TEXT-ALIGN: right">2444</td> <td style="TEXT-ALIGN: center">59</td> <td>Pak Yong Jin</td></tr> <tr style="HEIGHT: 16px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">614</td> <td style="TEXT-ALIGN: right">2444</td> <td style="TEXT-ALIGN: center">59</td> <td>Pak Nam Chol</td></tr></tbody></table>

hiker95 · Oct 3, 2011

Maij,

I assume that your raw data is in worksheet Sheet1 (without titles in row 1).

Sample raw data:

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

2443

28-Dec-09

6

Qassim BORHAN

Hamid Ismaeel KHALIFA

Khalid Saleh AL ZAKIBA

Mohammed AbdulRab AL YAZIDI

Fabio CESAR

Majid Imam HASSAN

Ibrahim MAJID

Talal AL BLOUSHI

Ahmed Faris AL BINALI

Ali Hassan AFIF

Bilal Mohammed RAJAB

2

2442

2-Jan-10

59

Jun Kwang Min

Ryang Mong Il

Kim Myong Gyu

Chae Tu Yong

Pak Song Chol

Choe Chol Man

Sing Yong Nam

Jon Kwak Ik

Yun Yong Il

Pak Yong Jin

Pak Nam Chol

3

2441

30-Dec-09

104

Mahamadou Sidib

Souleymane Diamoutn

Bakary Soumar

Amadou Sidib

Drissa Diakit

Mamadou Bagayoko

Lassana Fan

Mahamadou Diarra

Moustapha Yattabar

Tnma NDiaye

Mamadou Samassa

4

2439

18-Nov-09

64

Tome Pacovski

Goce Sedloski

Goran Popov

Nikolce Noveski

Slavco Georgievski

Dusan Savi

Velice Sumulikoski

Filip Ivanovski

Filip Despotovski

Robert Popov

Goran Pandev

5

2438

5-Sep-09

96

Ignatiy Nesterov

Aziz Ibrahimov

Stanislav Andreev

Azizbek Haydarov

Wagner Polodin

Anzur Ismailov

Anvar Gafurov

Server Djeparov

Aleksandr Geynrikh

Odil Ahmedov

Farhod Tadjiyev

6

2437

31-Aug-09

15

Abbas AHMED

Mohammed HUBAIL

Hussain BABA

Ebrahim AL MISHKHAS

Salman ISSA

Ahmed HAS'SAN

Mohammed SALMIN

Abdulwahab ALI

Mahmood ABDUL RAHMAN

Ismael ABDUL LATIF

Hussain ALI

7

2435

22-Nov-09

10

Amer Shafi Mahmoud

Hatem Aqal

Hassouneh Al Sheikh

Khalid Abdullah

Abdulla Deeb

Bassam Fathi

Mahmood Jamal

Amer Deeb Mohammed

Baha' Abdelrahman Mustafa

Anas Waleed Bani-Yassin

Mohammed Abdul Halim

8

2434

14-Nov-09

10

Amer Shafi Mahmoud

Hatem Aqal

Mahmoud Shilbaye

Hassouneh Al Sheikh

Khalid Abdullah

Abdulla Deeb

Bassam Fathi

M. Jamal

Amer Deeb Mohammed

Baha' Abdelrahman Mustafa

Anas Waleed Bani-Yassin

9

2433

17-Jun-09

8

LEE Woon Jae

OH Beom Seok

CHO Yong Hyung

PARK Ji Sung

KIM Jung Woo

PARK Chu Young

LEE Keun Ho

LEE Young Pyo

LEE Jung Soo

KI Sung Yueng

LEE Chung Yong

10

Sheet1

After the macro in a new worksheet Results (not all 99 rows are shown for brevity):

Excel Workbook

A

B

C

1

2443

6

Qassim BORHAN

2

2443

6

Hamid Ismaeel KHALIFA

3

2443

6

Khalid Saleh AL ZAKIBA

4

2443

6

Mohammed AbdulRab AL YAZIDI

5

2443

6

Fabio CESAR

6

2443

6

Majid Imam HASSAN

7

2443

6

Ibrahim MAJID

8

2443

6

Talal AL BLOUSHI

9

2443

6

Ahmed Faris AL BINALI

10

2443

6

Ali Hassan AFIF

11

2443

6

Bilal Mohammed RAJAB

12

2442

59

Jun Kwang Min

13

2442

59

Ryang Mong Il

14

2442

59

Kim Myong Gyu

15

2442

59

Chae Tu Yong

16

2442

59

Pak Song Chol

17

2442

59

Choe Chol Man

18

2442

59

Sing Yong Nam

19

2442

59

Jon Kwak Ik

20

2442

59

Yun Yong Il

21

2442

59

Pak Yong Jin

22

2442

59

Pak Nam Chol

23

2441

104

Mahamadou Sidib

24

2441

104

Souleymane Diamoutn

25

2441

104

Bakary Soumar

26

2441

104

Amadou Sidib

27

2441

104

Drissa Diakit

28

2441

104

Mamadou Bagayoko

29

2441

104

Lassana Fan

30

2441

104

Mahamadou Diarra

31

2441

104

Moustapha Yattabar

32

2441

104

Tnma NDiaye

33

2441

104

Mamadou Samassa

Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Option Explicit
Option Base 1
Sub ReorgData()
' hiker95, 10/03/2011
' http://www.mrexcel.com/forum/showthread.php?t=582700
Dim w1 As Worksheet, wR As Worksheet
Dim I(), O()
Dim LR As Long, LC As Long, r As Long, c As Long, n As Long
Set w1 = Worksheets("Sheet1")
LR = w1.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
LC = w1.Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
I = w1.Range("A1").CurrentRegion.Resize(, LC)
ReDim O(1 To (LC - 4 + 1) * LR, 1 To 3)
n = 0
For r = 1 To UBound(I) Step 1
  For c = 4 To LC Step 1
    n = n + 1
    O(n, 1) = I(r, 1)
    O(n, 2) = I(r, 3)
    O(n, 3) = I(r, c)
  Next c
Next r
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
wR.Range("A1").Resize(UBound(O), 3).Value = O
wR.UsedRange.Columns.AutoFit
wR.Activate
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

Transposing a row with a difference

Maij

New Member

Excel Facts

hiker95

Well-known Member

Maij

New Member

hiker95

Well-known Member

Maij

New Member

hiker95

Well-known Member

Maij

New Member

Maij

New Member

Maij

New Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

Transposing a row with a difference

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock