How to pull data from one sheet into new sheet based on two criteria

**rebeccanstewart** · 07-22-2013, 04:26 PM

Hello,

I receive a report that gives me the depreciation amount for the rest of the year based on dates. Each month it changes to how many months are left in the year.

I want to rearrange this data dump into an organized chart that shows each month depreciation by account.

Example:

Sheet 1:
Code Date Depreciation Amt
A 1-July 2000
A 1-Aug 2000
A 1-Sep 2000
A 1-Oct 2000
A 1-Nov 2000
A 1-Dec 2000

B 1-July 1000
B 1-Aug 1000
B 1-Sep 1000
B 1-Oct 1000
B 1-Nov 1000
B 1-Dec 1000

Sheet 2:
Jan Feb Ma Apr May Jun Jul Aug Sep Oct Nov Dec Total
A - - - - - - 2000 2000 2000 2000 2000 2000 12,000
B - - - - - - 1000 1000 1000 1000 1000 1000 6,000

Thank you

**oeldere** · 07-22-2013, 04:31 PM

I think you don;t need to rearange your data if the question is only to make a chart of the data.

You can also make a chart in which the data is vertical structured.

**rebeccanstewart** · 07-22-2013, 04:41 PM

the thing is. is that the first sheet is imported from a text document.

it doesn't have the label "a" for each amount. I manually filled it in.

I guess what I'm asking is how can I autofill based on the number of months are left in the year to label each month as "a" or "b"

from there I use a vlookup funtion to create a chart for sheet 2.

i want to know if there's a way that i can just create the chart from the data, or a way in vba to autofill down (based on the number of month) the label, then use a vlookup for sheet 2.

thanks again for replying!

**oeldere** · 07-22-2013, 04:56 PM

In that case it's time to post an excel file, without condentional information, how your data looks like.

Please also add the desired (expected) result in your file.

**rebeccanstewart** · 07-22-2013, 06:27 PM

ive attached the file thnx!

**oeldere** · 07-23-2013, 01:17 PM

I run the macro below to delete the blanc cells in column B an to fill data in column A.

After that I made a pivot table (verticaly)

After that I made a graph of it.

See the attached file.

Sub delete_blanc_cells_in_column_B_and_filldata_in_column_A()


 With Sheets("Projection")
    
   .Rows(6).EntireRow.Delete
   .Range("b1:b" & .Rows.Count).SpecialCells(4).EntireRow.Delete
  
    End With
    
'filldata_acending_incolumnA()

'from top to below (acending)

    Range(Range("a2"), Cells(Rows.Count, 3).End(xlUp).Offset(, -2)). _
        SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    With Columns("a")
        .Value = .Value
    End With
    
   
    
End Sub