reshap table

[mihai]

Hi

I want to format a table. In the attached file you will find the sourc
table and the desired format. I tryed to use Pivot Table but is no
suited for the problem.

Thank

+----------------------------------------------------------------
| Attachment filename: problem.xls
|Download attachment: http://www.excelforum.com/attachment.php?postid=361731
+----------------------------------------------------------------

 

[Dave Peterson]

A lot of people who read these newsgroups don't use excelforum. So they can't
see your attachment.

But even worse, many people wouldn't open an attachment anyway--too much threat
of malicious code.

 

[mihai]

In the end I succeeded to find a solution. Here is the cod (sorry bu
the table for which the cod is more complex than the example so the co
is more complex):

'Verific daca a lucrat si trec nr. ore
i = 2
For Each c In Worksheets("Ohne_Titel").Range("J2:J" & lastrow)
If c.Value <> 0 Then
rind = c.Row
cod = Cells(rind, 2).Value
zi = Cells(rind, 4).Value
Worksheets(1).Range("AA1:AA10000").Select
Selection.Find(what:=cod, after:=ActiveCell, lookat:=xlPart
searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False).Activate
rind1 = ActiveCell.Row
Worksheets(1).Range("AC1:BG1").Select
Selection.Find(what:=zi, after:=ActiveCell, lookat:=xlPart
searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False).Activate
col1 = ActiveCell.Column
Cells(rind1, col1).FormulaR1C1 = Cells(rind, 10).Value
Else
'Daca nu a lucrat verific ce concediu este
rind = c.Row
cod = Cells(rind, 2).Value
zi = Cells(rind, 4).Value
If Range("M" & i).Value <> 0 Then
Worksheets(1).Range("AA1:AA10000").Select
Selection.Find(what:=cod, after:=ActiveCell, lookat:=xlPart
searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False).Activate
rind1 = ActiveCell.Row
Worksheets(1).Range("AC1:BG1").Select
Selection.Find(what:=zi, after:=ActiveCell, lookat:=xlPart
searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False).Activate
col1 = ActiveCell.Column
Cells(rind1, col1).FormulaR1C1 = "CO"
Else
If Range("N" & i).Value <> 0 Then
Worksheets(1).Range("AA1:AA10000").Select
Selection.Find(what:=cod, after:=ActiveCell, lookat:=xlPart
searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False).Activate
rind1 = ActiveCell.Row
Worksheets(1).Range("AC1:BG1").Select
Selection.Find(what:=zi, after:=ActiveCell, lookat:=xlPart
searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False).Activate
col1 = ActiveCell.Column
Cells(rind1, col1).FormulaR1C1 = "DE"
Else
If Range("O" & i).Value <> 0 Then
Worksheets(1).Range("AA1:AA10000").Select
Selection.Find(what:=cod, after:=ActiveCell
lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False).Activate
rind1 = ActiveCell.Row
Worksheets(1).Range("AC1:BG1").Select
Selection.Find(what:=zi, after:=ActiveCell, lookat:=xlPart
searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False).Activate
col1 = ActiveCell.Column
Cells(rind1, col1).FormulaR1C1 = "CB"
Else
If Range("P" & i).Value <> 0 Then
Worksheets(1).Range("AA1:AA10000").Select
Selection.Find(what:=cod, after:=ActiveCell
lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext
MatchCase:=False).Activate
rind1 = ActiveCell.Row
Worksheets(1).Range("AC1:BG1").Select
Selection.Find(what:=zi, after:=ActiveCell
lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext
MatchCase:=False).Activate
col1 = ActiveCell.Column
Cells(rind1, col1).FormulaR1C1 = "CS"
Else
If Range("Q" & i).Value <> 0 Then
Worksheets(1).Range("AA1:AA10000").Select
Selection.Find(what:=cod, after:=ActiveCell
lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext
MatchCase:=False).Activate
rind1 = ActiveCell.Row
Worksheets(1).Range("AC1:BG1").Select
Selection.Find(what:=zi, after:=ActiveCell
lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext
MatchCase:=False).Activate
col1 = ActiveCell.Column
Cells(rind1, col1).FormulaR1C1 = "CM"
Else
If Range("R" & i).Value <> 0 Then
Worksheets(1).Range("AA1:AA10000").Select
Selection.Find(what:=cod, after:=ActiveCell
lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext
MatchCase:=False).Activate
rind1 = ActiveCell.Row
Worksheets(1).Range("AC1:BG1").Select
Selection.Find(what:=zi, after:=ActiveCell
lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext
MatchCase:=False).Activate
col1 = ActiveCell.Column
Cells(rind1, col1).FormulaR1C1 = "AN"
Else
If Range("S" & i).Value <> 0 Then
Worksheets(1).Range("AA1:AA10000").Select
Selection.Find(what:=cod
after:=ActiveCell, lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext
MatchCase:=False).Activate
rind1 = ActiveCell.Row
Worksheets(1).Range("AC1:BG1").Select
Selection.Find(what:=zi, after:=ActiveCell,
lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext,
MatchCase:=False).Activate
col1 = ActiveCell.Column
Cells(rind1, col1).FormulaR1C1 = "CF"
Else
If Range("T" & i).Value <> 0 Then

Worksheets(1).Range("AA1:AA10000").Select
Selection.Find(what:=cod,
after:=ActiveCell, lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext,
MatchCase:=False).Activate
rind1 = ActiveCell.Row
Worksheets(1).Range("AC1:BG1").Select
Selection.Find(what:=zi,
after:=ActiveCell, lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext,
MatchCase:=False).Activate
col1 = ActiveCell.Column
Cells(rind1, col1).FormulaR1C1 = "AM"
Else

Worksheets(1).Range("AA1:AA10000").Select
Selection.Find(what:=cod,
after:=ActiveCell, lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext,
MatchCase:=False).Activate
rind1 = ActiveCell.Row

Worksheets(1).Range("AC1:BG1").Select
Selection.Find(what:=zi,
after:=ActiveCell, lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext,
MatchCase:=False).Activate
col1 = ActiveCell.Column
Cells(rind1, col1).FormulaR1C1 =
"Error"
End If
End If
End If
End If
End If
End If
End If
End If
End If
i = i + 1
Next c