Matrix to List

[sim2ple]

Good Evening,

am stuck, doesn't happen a lot but I am.

Big fan of excelisfun, but wasn't able to find a solution on youtube. Hope this forum can help.

I would like to transfrom a matrix table into a list in order to import it into another program. I've found a solution with index but since I am working with a matrix that has 35000 values, this causes the workbook to crash and there must be a simpler solution.

File is available here: DOWNLOAD

Input sheet; here we enter the amount to be paid to various employees.

Transfer1: here, I combine all relevant info into one string and place it into the appropriate cell

Next step: now I need to eliminate all cells that are not having a value and extract the cells that have a value

one employee may have various entries

in this particular example; the final list should read:

<TABLE style="WIDTH: 227pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=302><COLGROUP><COL style="WIDTH: 227pt; mso-width-source: userset; mso-width-alt: 11044" width=302><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 227pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=302>001018/BASIC PAY\1166,66666666667</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>000951/BASIC PAY\1666,66666666667</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>000101/BASIC PAY\1250</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>001018/ACADEMY COACH\1200</TD></TR></TBODY></TABLE>

hope anyone has an answer, or is able to point me in the right direction.

thanks,

jochen

 

[Bradium]

Here is an example that may help you
It will list all the non empty cell values on a sheet called Sheet2.

Note the named range is MyData and refers to A2:C4
You can use the same code if you name your range MyData


Regards

Brad

Sub ListMyStrings()


Sheets("Sheet2").Select
    Range("A2").Select

For Each c In Range("MyData")

If c.Value <> "" Then
ActiveCell.Value = c.Value
ActiveCell.Offset(1, 0).Range("A1").Select
End If

Next c

End Sub

Sheet1

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 24px;"> <col style="width: 35px;"> <col style="width: 35px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td style="background-color: rgb(192, 192, 192); text-align: center;">Heading1</td> <td style="background-color: rgb(192, 192, 192); text-align: center;">Heading2</td> <td style="background-color: rgb(192, 192, 192); text-align: center;">Heading3</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="text-align: center;">ab</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">a</td> <td style="text-align: center;">b</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>
</td> <td style="text-align: center;">cd</td> <td>
</td> <td>
</td> <td style="text-align: center;">c</td> <td style="text-align: center;">d</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>
</td> <td>
</td> <td style="text-align: center;">ef</td> <td>
</td> <td style="text-align: center;">e</td> <td style="text-align: center;">f</td></tr></tbody></table>
<table style="font-size: 10pt; border-color: rgb(0, 255, 0); color: rgb(0, 0, 0); border-style: groove; font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>A2</td> <td>=E2&F2</td></tr> <tr> <td>B3</td> <td>=E3&F3</td></tr> <tr> <td>C4</td> <td>=E4&F4</td></tr></tbody></table></td></tr></tbody></table>



Sheet2

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td style="background-color: rgb(192, 192, 192);">MyList</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td>ab</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>cd</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>ef</td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

 

[sim2ple]

Hi,

thank you very much for your reply. Will try to work it tonight, but, from quickly looking at it, i think this works only if you have one entry per row. I have multiple entries per row?!

mhhhh, it's killing me.

Regards,

Jochen

 

[Bradium]

A more complete example with multiple entries per row using the same code.

HTH

Brad


Sheet1

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 24px;"> <col style="width: 34px;"> <col style="width: 34px;"> <col style="width: 34px;"> <col style="width: 34px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <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></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td style="background-color: rgb(192, 192, 192); text-align: center;">Heading1</td> <td style="background-color: rgb(192, 192, 192); text-align: center;">Heading2</td> <td style="background-color: rgb(192, 192, 192); text-align: center;">Heading3</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="text-align: center;">ab</td> <td style="text-align: center;">bu</td> <td style="text-align: center;">ux</td> <td>
</td> <td style="text-align: center;">a</td> <td style="text-align: center;">b</td> <td style="text-align: center;">u</td> <td style="text-align: center;">x</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>
</td> <td style="text-align: center;">cd</td> <td>
</td> <td>
</td> <td style="text-align: center;">c</td> <td style="text-align: center;">d</td> <td style="text-align: center;">v</td> <td style="text-align: center;">y</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="text-align: center;">eux</td> <td>
</td> <td style="text-align: center;">ef</td> <td>
</td> <td style="text-align: center;">e</td> <td style="text-align: center;">f</td> <td style="text-align: center;">w</td> <td style="text-align: center;">z</td></tr></tbody></table>
<table style="font-size: 10pt; border-color: rgb(0, 255, 0); color: rgb(0, 0, 0); border-style: groove; font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>A2</td> <td>=E2&F2</td></tr> <tr> <td>B2</td> <td>=F2&G2</td></tr> <tr> <td>C2</td> <td>=G2&H2</td></tr> <tr> <td>B3</td> <td>=E3&F3</td></tr> <tr> <td>A4</td> <td>=E4&C2</td></tr> <tr> <td>C4</td> <td>=E4&F4</td></tr></tbody></table></td></tr></tbody></table>



Sheet2

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td style="background-color: rgb(192, 192, 192);">MyList</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td>ab</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>bu</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>ux</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td>cd</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td>eux</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td>ef</td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4