Solved Show data in a particuar data entry in excel

October 9, 2012 at 00:27:17
Specs: Windows 7
good day.
I have data which I need to create report from it, sample:
A B C
1 EXPIRY DATE BALANCE ITEM
2 04/24/12 10,000 RED
3 01/25/12 50,000 BLUE
4 02/26/12 30,000 BLUE
5 03/11/12 10,000 WHITE
6 02/07/12 90,000 GREEN
SHEET1 -REFERENCE(DATA)

JAN FEB MAR APR
BLUE BLUE WHITE RED
GREEN
SHEET2 - REPORT TO CREATE

Above are the sample of my reference and the report I should create.
I have data that has DATE, BALANCE and ITEM, my boss want a report that summarized all the items that will expire on a monthly basis. Is this possible?
Thank you very much!


See More: Show data in a particuar data entry in excel

Report •

#1
October 9, 2012 at 13:57:48
Please click on the following line, read the instructions found via that link and then repost your example data. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 10, 2012 at 17:48:09
good day.
This is my revised question hope I stated it more clearly.
I have data which I need to create report from it, sample:
              A                     B         C
        1   EXPIRY DATE          BALANCE      ITEM
        2   03/24/12             10,000       RED
        3   01/25/12             50,000       BLUE
        4   02/26/12             30,000       BLUE
        5   03/11/12             10,000       WHITE
        6   02/07/12             90,000       GREEN
           SHEET1 -REFERENCE(DATA)
          A          B           C        
      1  JAN        FEB         MAR      
      2  BLUE       BLUE        RED    
                    GREEN       WHITE
          SHEET2 - REPORT TO CREATE

Above are the sample of my reference and the report I should create.
I have data that has DATE, BALANCE and ITEM, my boss want a report that summarized
all the items that will expire on a monthly basis.
Is this possible?
Thank you very much!


Report •

#3
October 11, 2012 at 12:36:52
✔ Best Answer
Based on your example data, this code should give you the results you are
looking for.

Please note that the first thing it does is clear the contents of Sheet 2. It then
places the Month names in Sheet 2 Row 1 and begins to bring in the data from
Sheet 1.

This allows the code to give you a full update each time it is run to ensure that it
reflects any changes that were made to Sheet 1.

I suggest that you test this in a back up copy of your workbook since macros can
not be easily undone.


Sub ExpiryByMonth()
'Set up Sheet 2 with Month Names in Row 1
Sheets(2).Cells.ClearContents
  For myMonth = 1 To 12
     With Sheets(2).Cells(1, myMonth)
       .FormulaR1C1 = myMonth & "/1/2012"
       .NumberFormat = "mmm"
     End With
  Next
'Determine last Row with data in Sheet 1, Column A
  lastRw = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
'Loop through rows in Sheet 1 (Source rows)
   For srcRw = 2 To lastRw
'Determine Month of source data
    srcMonth = Month(Sheets(1).Cells(srcRw, 1))
'Determine next empty in Sheet 2 for Month of date from Sheet 1 (Destination Row)
     nxtRw = Sheets(2).Cells(Rows.Count, srcMonth).End(xlUp).Row + 1
'Copy data from Sheet 1 to Sheet 2
      Sheets(2).Cells(nxtRw, srcMonth) = Cells(srcRw, 3)
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 16, 2012 at 02:22:21
This is a big Help, Thank you very much!

Report •

#5
October 16, 2012 at 21:17:33
Hey, I have been facing some problem like that in excel.. thanks... this helps a lot..

Report •

#6
October 21, 2012 at 18:53:51
Hi DerbyDad,

I would like to ask, if I want to change the column of Expiry date from column A to column D?
What will I have to change?

Thanks a lot!


Report •

#7
October 22, 2012 at 06:12:41
When using the Cells method, the arguments are:

Cells(row_num, column_num)

A column_num of 1 refers to Column A, therefore a column_num of 4 would refer to Column D.

I've changed it in the 2 places it needed to be changed below.


Sub ExpiryByMonth()
'Set up Sheet 2 with Month Names in Row 1
Sheets(2).Cells.ClearContents
  For myMonth = 1 To 12
     With Sheets(2).Cells(1, myMonth)
       .FormulaR1C1 = myMonth & "/1/2012"
       .NumberFormat = "mmm"
     End With
  Next
'Determine last Row with data in Sheet 1, Column D
  lastRw = Sheets(1).Cells(Rows.Count, 4).End(xlUp).Row
'Loop through rows in Sheet 1 (Source rows)
   For srcRw = 2 To lastRw
'Determine Month of source data
    srcMonth = Month(Sheets(1).Cells(srcRw, 4))
'Determine next empty in Sheet 2 for Month of date from Sheet 1 (Destination Row)
     nxtRw = Sheets(2).Cells(Rows.Count, srcMonth).End(xlUp).Row + 1
'Copy data from Sheet 1 to Sheet 2
      Sheets(2).Cells(nxtRw, srcMonth) = Cells(srcRw, 3)
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
October 22, 2012 at 19:00:20
This is great! My boss will like it, thank you very much this is a big help. Thank you!

Report •

#9
October 22, 2012 at 19:24:54
DerdyDad,

I have small problem with this, I would like to ask what if I have dates that has a year, sample if I have to extend my report from Jan-Dec -12 to Jan-Dec-13?
As I run this command, all items fall in one particular month even if they have different year.
I would like to separate(if possible) the data per year then per month.
What will I have to change?
Thanks in advance!


Report •

#10
October 23, 2012 at 09:36:46
This additional requirement will take a little time. This is not as simple a fix as just changing a column like you asked for in your first change request.

Allow me to offer a posting tip...

When asking for help in a forum such as this, you should gather all of your requirements before posting.

The solutions offered typically require the setting up of a test workbook, then the writing of code and then the testing of the code. Each time you come back to the forum with more and/or different requirements, we usually end up having recreate the test environment, modify the code and then retest it.

I am now into the 3rd round of set up and testing since you have changed your requirements twice.

Imagine if this help was not free. Imagine if you hired a consultant, gave them your requirements, and then kept changing them each time they presented a solution that met the previous set of requirements. Imagine your boss not being happy as the consultant kept billing him for each change request.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
October 29, 2012 at 00:07:35
DerbyDad,

My Apology of not being more clearly on what result I really need.
And now I understand the hardship of being a consultant like you.
I think what I had requested was a very helpful for my report, you don't need to answer my last question.
I'm very thankful to all the help I've been receiveing from you.
Again, please accept my sincere apology.

Thanks.


Report •

Ask Question