Excel

Hi
I am trying to transfer data from Sheet 1 To sheet 2 on multiple condition.
first criteria is the same date, then same name and same product then the value should be linked to sheet2

Thanks
test.xls
surah79Asked:
Who is Participating?
 
5teveoConnect With a Mentor Commented:
Ok I added a macro that will format data as you need to allow you to generate a pivot table as discussed earlier. The macro will cycle thru all rows and all you to add additional columns of fabric data if needed. See attached xlsm file to play with

Once in Pivot Table mode you can do neat things.

Sub Macro2()
'
' Macro2 Macro
'

'
     Set objWB = ActiveWorkbook
    Set objData = ActiveSheet
    On Error Resume Next
    Set objReport = objWB.Sheets("Sheet1-New")
    If Err.Number <> 0 Then
        Err.Clear
        On Error GoTo 0
        Set objReport = objWB.Worksheets.Add(, objWB.Sheets(objWB.Sheets.Count))
        objReport.Name = "Sheet1-New"
        objReport.Cells(1, "A").Value = "Name"
        objReport.Cells(1, "B").Value = "Date"
        objReport.Cells(1, "C").Value = "Product"
        objReport.Cells(1, "D").Value = "Qty"
        objReport.Rows("1:1").Font.Bold = True
    End If
    On Error GoTo 0
    intReportRowOut = 2

   'Cycle thru all Rows starting at 2
    For intRow = 2 To objData.Cells(65536, "A").End(xlUp).Row
        'Cycle thru all columns starting at 3
        For intColDates = 3 To objData.Cells(1, Columns.Count).End(xlToLeft).Column
            If IsDate(objData.Cells(intRow, "B").Value) = True Then
              'Name
               objReport.Cells(intReportRowOut, "A").Value = objData.Cells(intRow, "A").Value
              'Date
               objReport.Cells(intReportRowOut, "B").Value = objData.Cells(intRow, "B").Value
              'Product
               objReport.Cells(intReportRowOut, "C").Value = objData.Cells(1, intColDates).Value
              'Qty
               objReport.Cells(intReportRowOut, "D").Value = objData.Cells(intRow, intColDates).Value
            End If
            intReportRowOut = intReportRowOut + 1
        Next

    Next

End Sub
EE20130103PivotOption.xlsm
0
 
5teveoCommented:
If you have any control of data - Make a small change to data format and create a pivot table!

see example...

I created Sheet1-New as new data source with small data change...

and Sheet2-New which closely matches your format

Hope this helps...
EE20130103PivotOption.xls
0
 
perolinCommented:
I agree with 5teveo.

Use Pivot, it is great for this problem.

And if you are more interested in a daily view, with just 3 clicks your table is changed.
EE20130103PivotOption-Daily.xls
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
surah79Author Commented:
Hi 5teveo\5teveo

Sorry guys to reply u late, but I can't change the way the source data is as it is linked to different spreadsheets for other reporting, is there any other formula i can use to link it.
Thanks
0
 
surah79Author Commented:
Thanks a lot .
0
 
5teveoCommented:
Good Luck! Thanks for points!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.