We help IT Professionals succeed at work.

Extract Data for Reporting

Cartillo
Cartillo used Ask the Experts™
on
Hi Experts,

I would like to request Experts help create a macro to copy data from “Order” sheet to “Report” (at column C to I) sheet based on entered value (Date) at Cell B2 and B3 at “Report” sheet.

I have manually copied few data for Experts to get better view at “Report” sheet. The copied data need to align/displayed properly for easy view. Hope Experts will help me to create this feature.


 

OrderList.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Enter this in C5 and copy across

=IF(ISNUMBER(B5),IF(B5+1>$B$3,"",B5+1),$B$2)

And enter this in C6 and copy down and across

=IF(ISNA(MATCH(Report!C$5,Order!$A$2:$ABD$2,0)),"",IF(INDEX(Order!$A$2:$ABD$146,MATCH(Report!$B6,Order!$A$2:$A$146,0),MATCH(Report!C$5,Order!$A$2:$ABD$2,0))="","",INDEX(Order!$A$2:$ABD$146,MATCH(Report!$B6,Order!$A$2:$A$146,0),MATCH(Report!C$5,Order!$A$2:$ABD$2,0))))
Slightly reduced formula for D6

=IF(ISNA(MATCH(Report!C$5,Order!$A$2:$ABD$2,0)),"",IF(OFFSET(Order!$A3,0,MATCH(Report!C$5,Order!$A$2:$ABD$2,0))="","",OFFSET(Order!$A3,0,MATCH(Report!C$5,Order!$A$2:$ABD$2,0))))

Author

Commented:
Hi ssaqibh,

Thanks for formula, is that possible with a macro? This is to allow only search value are displayed. Whenever we perform a new search, the old data need to clear and replaced it with a new one. Hope this is feasible.
You should not need a macro because this formula does exactly what you want. But if you must have a macro then try this.
Sub ExtReport()
    Range("$C$6:$I$149").FormulaR1C1 = _
        "=IF(R5C="""","""",IF(OFFSET(Order!R[-3]C1,0,MATCH(Report!R5C,Order!R2C1:R2C732,0))="""","""",OFFSET(Order!R[-3]C1,0,MATCH(Report!R5C,Order!R2C1:R2C732,0))))"
    Range("C5:I5").FormulaR1C1 = _
        "=IF(ISNUMBER(RC[-1]),IF(RC[-1]+1>R3C2,"""",RC[-1]+1),R2C2)"
    Range("$C$6:$I$149").Value = Range("$C$6:$I$149").Value
End Sub

Open in new window

Author

Commented:
Hi ssaqibh,

Thanks for the code, I have tested and its not copying the whole data. Attached the sample file. Is that possible to align the data at the center so that its visible for reader.  
OrderList.xlsm
The problem is that the data in the order form is starting on the second line and not on the first line. You will have to correct your data on the order form or better to increase the row height to be able to see the data.

Author

Commented:
Hi ssaqibh,

Thanks for the tips. Also noticed the last data row always missing, e.g. if I select 1-5 Jan, only data 1-4 copied, 5th Jan's data were missing. Same like if I select 1-jan-7-jan, only 1-6jan copied, data for 7-Jan has been missing. How to fix this? Sorry for the trouble.
I do not see why the last column is missing. It works fine with me. Anyways I have changed the formula for the date row for some other reason. If there is still a problem then post a file showing the problem.

I have also modified the code to

- Automatically adjust the row heights so that all the data is visible.
- Enable the program to run from any sheet.

Saqib

Author

Commented:
Hi Saqib,

Here's the sample file, column H wasn't update even though the date is apart of query.
OrderList.xlsm
See cell B3 on Report sheet. It contains 4-Jan as the end date.

The columns data will not go past 4-Jan.

Author

Commented:
Hi ssaqibh,

The file that I've pushed is actually having a different date, start date 1-Jan and End Date is 7-Jan. Probably you're looking at a wrong file

Yes, you are right, it was the wrong file.

Here is the revised code.
Sub ExtReport()
    Range("$C$6:$I$149").FormulaR1C1 = _
        "=IF(R5C="""","""",IF(OFFSET(Order!R[-3]C1,0,MATCH(Report!R5C,Order!R2C1:R2C732,0)-1)="""","""",OFFSET(Order!R[-3]C1,0,MATCH(Report!R5C,Order!R2C1:R2C732,0)-1)))"
    Range("C5:I5").FormulaR1C1 = _
        "=IF(ISNUMBER(RC[-1]),IF(RC[-1]+1>R3C2,"""",RC[-1]+1),R2C2)"
    Range("$C$6:$I$149").Value = Range("$C$6:$I$149").Value
End Sub

Open in new window

Author

Commented:
Hi ssaqibh,

Thanks a lot for the revised code. Is that possible to make the date selection flexible,  minimum search is single date query and the maximum is 7 days. When I select 1-3Jan, the result returned with a duplication data. Attached the sample for detail.
OrderList-V1.xlsm

Sub ExtReport()
    Range("$C$6:$I$149").FormulaR1C1 = _
        "=IF(R5C="""","""",IF(OFFSET(Order!R[-3]C1,0,MATCH(Report!R5C,Order!R2C1:R2C732,0)-1)="""","""",OFFSET(Order!R[-3]C1,0,MATCH(Report!R5C,Order!R2C1:R2C732,0)-1)))"
    Range("C5:I5").FormulaR1C1 = _
        "=IF(R2C2+COLUMN()-3>R3C2,"""",R2C2+COLUMN()-3)"
    Range("$C$5:$I$149").Value = Range("$C$5:$I$149").Value
    Range("$C$5:$I$149").EntireRow.AutoFit
End Sub

Open in new window

Author

Commented:
Hi,
Thanks a lot for the superb code:)