Row to column conversion

  • Thread starter Thread starter tr2usa
  • Start date Start date
T

tr2usa

How can I get below info to show as:

A1 B1 C1
Item number 894011 Bag Poly w/snaps for 6x9 mats

Requirement date Month Calculated Opening Inv.
8/1/2008 August 0
9/1/2008 September -624
12/1/2008 December 2,076
1/1/2009 January 1,932
3/1/2009 March 1,452
==================================================================================
Like this


Aug. Sept October Nov. Dec
Item number 894011 Bag Poly w/snaps for 6x9 mats 0
-624 #N/A #N/A 2076

Some months are in order some of them are not the way you see above.

thanks
Vedat
 
It is hard to give you a good answer because I don't know for how many items
you need to perform this conversion for and if you are doing it to multiple
items the spacing and header rows for each item. There is one problem that
need to be fixed no matter which methoid you use. Your month names either
need to be spellout completely or use the standard 3 letter abrevation for
each month name in both the source table and where you want the results to go.

It would also help to give the sheet name(s) and the rows and columns where
the data is located.
 
It is hard to give you a good answer because I don't know for how many items
you need to perform this conversion for and if you are doing it to multiple
items the spacing and header rows for each item.  There is one problem that
need to be fixed no matter which methoid you use.  Your month names either
need to be spellout completely or use the standard 3 letter abrevation for
each month name in both the source table and where you want the results to go.

It would also help to give the sheet name(s) and the rows and columns where
the data is located.










- Show quoted text -
Click to expand...

Thank you, I know my data was short or not clear.

My item numbers are 5 digits(B1747) that is why I put B1747>100 and
formula.
I use this formula: =IF(B1747>100,IF(B1758="August",I1758),""), if
August is not in the line, It does not work.

I have 6635 lines and trying to pull 485 items out of those 6635
lines. Spaces are specific.

Sheet name is 01 Inventory.

Do these info helps?
Vedat
 
See if this code works. I tried to make the code handle every case I could
think of to prevent errors. This code is prety robust. The code creates a
summary sheet. if you run the code twice you must remove the summary sheet
manually.

Sub CreateSummary()
StartMonth = 8

Set SumSht = Sheets.Add(after:=Sheets(Sheets.Count))
SumSht.Name = "Summary"
'Create Header Row
For MonthNumber = 0 To 11
SumSht.Cells(1, MonthNumber + 4) = _
MonthName(((StartMonth + MonthNumber - 1) Mod 12) + 1)
Next MonthNumber

SumRowCount = 1
With Sheets("01 Inventory")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For InventRowCount = 1 To LastRow
If .Range("A" & InventRowCount) = "Item number" Then
SumRowCount = SumRowCount + 1
.Range("A" & InventRowCount & _
":C" & InventRowCount).Copy _
Destination:=SumSht.Range("A" & SumRowCount)
End If
If IsDate(.Range("A" & InventRowCount)) Then
'find the correct column for the month name
NameofMonth = .Range("B" & InventRowCount)
Inventory = .Range("C" & InventRowCount)
With SumSht
Set c = .Rows(1).Find(what:=NameofMonth, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Month : " & NameofMonth)
Else
.Cells(SumRowCount, c.Column) = Inventory
End If
End With
End If
Next InventRowCount
End With
End Sub
 
See if this code works.  I tried to make the code handle every case I could
think of to prevent errors.  This code is prety robust.  The code creates a
summary sheet.  if you run the code twice you must remove the summary sheet
manually.

Sub CreateSummary()
StartMonth = 8

Set SumSht = Sheets.Add(after:=Sheets(Sheets.Count))
SumSht.Name = "Summary"
'Create Header Row
For MonthNumber = 0 To 11
   SumSht.Cells(1, MonthNumber + 4) = _
      MonthName(((StartMonth + MonthNumber - 1) Mod 12) + 1)
Next MonthNumber

SumRowCount = 1
With Sheets("01 Inventory")
   LastRow = .Range("A" & Rows.Count).End(xlUp).Row
   For InventRowCount = 1 To LastRow
      If .Range("A" & InventRowCount) = "Item number" Then
         SumRowCount = SumRowCount + 1
         .Range("A" & InventRowCount & _
            ":C" & InventRowCount).Copy _
            Destination:=SumSht.Range("A" & SumRowCount)
      End If
      If IsDate(.Range("A" & InventRowCount)) Then
        'find the correct column for the month name
        NameofMonth = .Range("B" & InventRowCount)
        Inventory = .Range("C" & InventRowCount)
        With SumSht
           Set c = .Rows(1).Find(what:=NameofMonth, _
              LookIn:=xlValues, lookat:=xlWhole)
           If c Is Nothing Then
              MsgBox ("Cannot find Month : " & NameofMonth)
           Else
              .Cells(SumRowCount, c.Column) = Inventory
           End If
        End With
      End If
   Next InventRowCount
End With
End Sub










- Show quoted text -
Click to expand...

Thank you very much, It worked.
 
Back
Top