index/match?!

[gledister]

we have:
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64 align=right>20</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64 align=right>30</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>a</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2</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>b</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>4</TD></TR></TBODY></TABLE>

at the end we must have:
<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=64>20a</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>20b</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>30a</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>30b</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" class=xl65 height=20>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>4</TD></TR></TBODY></TABLE>

How can we do that?!

Thanks in advance!

 

[gledister]

yes it uses "," for decimals...

 

[gledister]

still does not work..

 

[MrKowz]

still does not work..

I think I might know what is going on... since your Excel uses "," for decimal instead of ".", the 9.99E+307 is erroring out.

Try the following formula, adjusted for your locale (sorry, I rarely work with locales other than US/UK, so getting it to work can sometimes be a bit of a hassle):

{=INDEX($A:$H;MATCH(LEFT(J2;FIND("$$";SUBSTITUTE(J2;" ";"$$";LEN(SUBSTITUTE(J2;" ";"$$"))-LEN(J2)))-1);A:A;0);MATCH(RIGHT(J2;LEN(J2)-FIND("$$";SUBSTITUTE(J2;" ";"$$";LEN(SUBSTITUTE(J2;" ";"$$"))-LEN(J2))));TEXT(OFFSET(INDEX(A:A;MATCH(LEFT(J2;FIND("$$";SUBSTITUTE(J2;" ";"$$";LEN(SUBSTITUTE(J2;" ";"$$"))-LEN(J2)))-1);A:A;0));MATCH(9,99E+307;IF($A$1:INDEX(A:A;MATCH(LEFT(J2;FIND("$$";SUBSTITUTE(J2;" ";"$$";LEN(SUBSTITUTE(J2;" ";"$$"))-LEN(J2)))-1);A:A;0))="";1;""))-MATCH(LEFT(J2;FIND("$$";SUBSTITUTE(J2;" ";"$$";LEN(SUBSTITUTE(J2;" ";"$$"))-LEN(J2)))-1);A:A;0);0;1;COLUMNS($A:$H));"@");0))}
Again, CTRL+SHIFT+ENTER.

 

[gledister]

its not working for me...I mean what you are doing probably is just to get numbers from the tables by indexing it...I want that column J and K comes up directly automatically...because creating manually "J" would be terryfing...I have been using the macro hiker95 gave me in this topic and it works perfectly for just one row and it does it all automatically...what I probably want is that macro "upgraded" in this more difficult complex problem...

 

[gledister]

excuse me it works for one line only...not one row at all

 

[gledister]

*bump*

 

[gledister]

*bump*

 

[gledister]

*bump*

 

[hiker95]

gledister,

I received your 2 Private Messages.

When your raw data and results change, I would suggest that you start a New Post.

Be back in a little while.

 

[hiker95]

gledister,


Sample raw data before the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		6,5	8,5	9	9,5	10
2	NBWR826TS B	1	3	2	2	1
3		S	M	L	XL
4	NBMRJ0316 BK	10	15	12	5
5	NBMRM1073EWT	1	2	5	2
6
7
8
9
10
11
12
13
14
15
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		6,5	8,5	9	9,5	10
2	NBWR826TS B	1	3	2	2	1			NBWR826TS B 6,5	1
3		S	M	L	XL				NBWR826TS B 8,5	3
4	NBMRJ0316 BK	10	15	12	5				NBWR826TS B 9	2
5	NBMRM1073EWT	1	2	5	2				NBWR826TS B 9,5	2
6									NBWR826TS B 10	1
7									NBMRJ0316 BK S	10
8									NBMRJ0316 BK M	15
9									NBMRJ0316 BK L	12
10									NBMRJ0316 BK XL	5
11									NBMRM1073EWT S	1
12									NBMRM1073EWT M	2
13									NBMRM1073EWT L	5
14									NBMRM1073EWT XL	2
15
Sheet1

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.

Option Explicit
Sub ReorgDataV3()
' hiker95, 07/13/2011
' http://www.mrexcel.com/forum/showthread.php?t=540947&page=2
Dim LC As Long, SC As Long, a As Long, aa As Long, NR As Long
Dim Area As Range, SR As Long, ER As Long
Application.ScreenUpdating = False
LC = Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
SC = LC + 3
NR = 1
For Each Area In Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    If .Rows.Count = 1 Then
      SR = .Row
      For aa = 2 To LC Step 1
        If Cells(SR, aa) <> "" Then
          NR = NR + 1
          Cells(NR, SC) = Cells(SR, 1) & " " & Cells(SR - 1, aa)
          Cells(NR, SC + 1) = Cells(SR, aa)
        End If
      Next aa
    Else
      SR = .Row
      ER = SR + .Rows.Count - 1
      For a = SR To ER Step 1
        For aa = 2 To LC Step 1
          If Cells(a, aa) <> "" Then
            NR = NR + 1
            Cells(NR, SC) = Cells(a, 1) & " " & Cells(SR - 1, aa)
            Cells(NR, SC + 1) = Cells(a, aa)
          End If
        Next aa
      Next a
    End If
  End With
Next Area
Columns(SC).Resize(, 2).AutoFit
Application.ScreenUpdating = True
End Sub

Then run the ReorgDataV3 macro.