Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel

Posted on 2013-01-03
6
Medium Priority
?
305 Views
Last Modified: 2013-01-09
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
0
Comment
Question by:surah79
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:5teveo
ID: 38742764
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
 
LVL 2

Expert Comment

by:perolin
ID: 38743101
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
 

Author Comment

by:surah79
ID: 38749087
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 8

Accepted Solution

by:
5teveo earned 2000 total points
ID: 38750469
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
 

Author Closing Comment

by:surah79
ID: 38760625
Thanks a lot .
0
 
LVL 8

Expert Comment

by:5teveo
ID: 38761606
Good Luck! Thanks for points!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question