Solved

Excel

Posted on 2013-01-03
6
298 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This video Micro Tutorial explains how to clone a hard drive using a commercial software product for Windows systems called Casper from Future Systems Solutions (FSS). Cloning makes an exact, complete copy of one hard disk drive (HDD) onto another d…
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…

810 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