Transpose data records

B

boldcode

Active Member
Joined
Mar 12, 2010
Messages
347
Hi all,

I will describe below what I am looking for:

I have the following set of data (just using an sample of the actual data) with two columns and their titles "id" for Column A and "compcode" for Column B:

<table style="border-collapse: collapse; width: 426px; height: 296px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 66pt;" width="88"> <col style="width: 36pt;" width="48"> <col style="width: 48pt;" width="64"> <col style="width: 53pt;" width="71"> <col style="width: 76pt;" width="101"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 66pt;" height="20" width="88">
</td> <td style="width: 36pt;" width="48">
</td> <td class="xl65" style="width: 48pt;" width="64">ColumnA</td> <td class="xl65" style="width: 53pt;" width="71">
</td> <td style="width: 76pt;" width="101">Column B</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Row 1</td> <td>
</td> <td class="xl65">id</td> <td class="xl65">
</td> <td class="xl66">compcode</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Row 2</td> <td>
</td> <td class="xl65">1</td> <td class="xl65">
</td> <td class="xl66">FRBRR81AB1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Row 3</td> <td>
</td> <td class="xl65">1</td> <td class="xl65">
</td> <td class="xl66">company</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Row 4</td> <td>
</td> <td class="xl65">1</td> <td class="xl65">
</td> <td class="xl66">HQ</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Row 5</td> <td>
</td> <td class="xl65">1</td> <td class="xl65">
</td> <td class="xl66">division A</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Row 6</td> <td>
</td> <td class="xl65">1</td> <td class="xl65">
</td> <td class="xl66">branch A</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Row 7</td> <td>
</td> <td class="xl65">1</td> <td class="xl65">
</td> <td class="xl66">texas</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Row 8</td> <td>
</td> <td class="xl65">2</td> <td class="xl65">
</td> <td class="xl66">FRBAUIUUA890</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Row 9</td> <td>
</td> <td class="xl65">2</td> <td class="xl65">
</td> <td class="xl66">company b</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Row 10</td> <td>
</td> <td class="xl65">2</td> <td class="xl65">
</td> <td class="xl66">HQ</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Row 11</td> <td>
</td> <td class="xl65">2</td> <td class="xl65">
</td> <td class="xl66">DIVISION B</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Row 12</td> <td>
</td> <td class="xl65">2</td> <td class="xl65">
</td> <td class="xl66">Branch B</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Row 13</td> <td>
</td> <td class="xl65">2</td> <td class="xl65">
</td> <td class="xl66">texas</td> </tr> </tbody></table>
I want to transpose this data to look like the example below:

<table style="border-collapse: collapse; width: 554pt;" border="0" cellpadding="0" cellspacing="0" width="737"><col style="width: 48pt;" width="64"> <col style="width: 15pt;" width="20"> <col style="width: 48pt;" width="64"> <col style="width: 14pt;" width="19"> <col style="width: 76pt;" width="101"> <col style="width: 19pt;" width="25"> <col style="width: 56pt;" width="74"> <col style="width: 22pt;" width="29"> <col style="width: 48pt;" width="64"> <col style="width: 20pt;" width="26"> <col style="width: 56pt;" width="75"> <col style="width: 19pt;" width="25"> <col style="width: 48pt;" width="64"> <col style="width: 17pt;" width="23"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" height="20" width="64">
</td> <td style="width: 15pt;" width="20">
</td> <td style="width: 48pt;" width="64">ColumnA</td> <td style="width: 14pt;" width="19">
</td> <td style="width: 76pt;" width="101">ColumnB</td> <td style="width: 19pt;" width="25">
</td> <td style="width: 56pt;" width="74">ColumnC</td> <td style="width: 22pt;" width="29">
</td> <td style="width: 48pt;" width="64">ColumnD</td> <td style="width: 20pt;" width="26">
</td> <td style="width: 56pt;" width="75">Column E</td> <td style="width: 19pt;" width="25">
</td> <td style="width: 48pt;" width="64">Column F</td> <td style="width: 17pt;" width="23">
</td> <td style="width: 48pt;" width="64">Column G</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Row 1</td> <td>
</td> <td align="right">1</td> <td>
</td> <td>FRBRR81AB1</td> <td>
</td> <td>company</td> <td>
</td> <td>HQ</td> <td>
</td> <td>division A</td> <td>
</td> <td>branch A</td> <td>
</td> <td>texas</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Row 2</td> <td>
</td> <td align="right">2</td> <td>
</td> <td>FRBAUIUUA890</td> <td>
</td> <td>company b</td> <td>
</td> <td>HQ</td> <td>
</td> <td>DIVISION B</td> <td>
</td> <td>Branch B</td> <td>
</td> <td>texas</td> </tr> </tbody></table>
I can't seem to figure out the right formula or visual basic code to this. Let if me know if you need further clarification.

Thanks in Advance,

BC
 
Last edited:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Here is the data:
Excel Workbook
AB
1idcompcode
21FRBRR81AB1
31company
41HQ
51division A
61branch A
71texas
82FRBAUIUUA890
92company b
102HQ
112DIVISION B
122Branch B
132texas
Sheet2
Excel 2010

Here is the returned data:
Excel Workbook
DEFGHIJKL
1idCountReturned 1Returned 2Returned 3Returned 4Returned 5Returned 6Returned 7
216FRBRR81AB1companyHQdivision Abranch Atexas
326FRBAUIUUA890company bHQDIVISION BBranch Btexas
Sheet2
Excel 2010

Formula Entered cell E2, then copy down:

=COUNTIF($A$2:$A$13,D2)

Formula Entered cell F2, then copy down (must use Ctrl + Shift + Enter):

=IF(COLUMNS($F2:F2)>$E2,"",INDEX($B$2:$B$13,SMALL(IF($A$2:$A$13=$D2,ROW($A$2:$A$13)-ROW($A$2)+1),COLUMNS($F2:F2))))
Excel Workbook
F
2FRBRR81AB1
Sheet2
Excel 2010
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Or if you are using Excel 2010 use this non-array formula:

=IF(COLUMNS($F2:F2)>$E2,"",INDEX($B$2:$B$13,AGGREGATE(15,6,(ROW($A$2:$A$13)-ROW($A$2)+1)/($A$2:$A$13=$D2),COLUMNS($F2:F2))))
Excel Workbook
F
2FRBRR81AB1
Sheet2
Excel 2010
Cell Formulas
RangeFormula
F2=IF(COLUMNS($F2:F2)>$E2,"",INDEX($B$2:$B$13,AGGREGATE(15,6,(ROW($A$2:$A$13)-ROW($A$2)+1)/($A$2:$A$13=$D2),COLUMNS($F2:F2))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
VBA for Web Scraping help
Replies
0
Views
593
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
M
  • Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
S
  • Question
How to click a link inside a table using VBA
Replies
0
Views
2K
swapnilk
S
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
398
AndyEd
A

Forum statistics

Threads
1,214,940
Messages
6,122,361
Members
449,080
Latest member
Armadillos

Share this page

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
Back
Top