Solved

Excel

Posted on 2013-01-03
6
301 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

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…
This collection of functions covers all the normal rounding methods of just about any numeric value.
Windows 8 comes with a dramatically different user interface known as Metro. Notably missing from the new interface is a Start button and Start Menu. Many users do not like it, much preferring the interface of earlier versions — Windows 7, Windows X…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

710 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