Solved

Excel

Posted on 2013-01-03
6
296 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 8

Accepted Solution

by:
5teveo earned 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Ever notice how you can't use a new drive in Windows without having Windows assigning a Disk Signature?  Ever have a signature collision problem (especially with Virtual Machines?)  This article is intended to help you understand what's going on and…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now