Results 1 to 7 of 7

Thread: Index and match vertical and horizontal data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2010
    Posts
    179
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Index and match vertical and horizontal data

    I have two excel sheets
    Sheet 1
    productID 18-5-2019 17-5-2019 16-5-2019 15-5-2019 for5 months....
    32852314981
    =? =
    32796241751
    32918411783
    requirement
    32852314981
    100 0 25 0
    sheet 2
    has transaction data
    productid date quantity
    32852314981
    18-5-2019 100
    32852314981
    16-05-019
    25
    32852314981
    13-05-019
    1
    32796241751
    15-5-2019 500
    32796241751 12-5-2019 200
    32796241751 13-5-2019 7

    ****** id="cke_pastebin" style="position: absolute; top: 224px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
    18-5-2019
    RequiremenRequirement - I want to get the quantity sold for a specific product matched with the date and quantity in sheet 2 data
    To Note - when the quantity for specific date in not available in sheet 2 - it can be give as n/a


    Thank you

  2. #2
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,697
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index and match vertical and horizontal data

    Hi,

    Below set up across A1:I7 for illustrative purposes only. Please change ranges to suit your actual data set.

    productID 18/05/2019 17/05/2019 16/05/2019 15/05/2019 productid date quantity
    32852314981 100 0 25 0 32852314981 18/05/2019 100
    32796241751 0 0 0 500 32852314981 16/05/2019 25
    32918411783 0 0 0 0 32852314981 13/05/2019 1
    32796241751 15/05/2019 500
    32796241751 12/05/2019 200
    32796241751 13/05/2019 7

    Formula in B2, copied down and across, is:

    Code:
    =IFERROR(INDEX($I$2:$I$7,MATCH(1,IF($G$2:$G$7=$A2,IF($H$2:$H$7=B$1,1)),0)),0)
    Which needs committing with CTRL+SHIFT+ENTER due to its array nature.

    Matty

  3. #3
    Board Regular
    Join Date
    Mar 2010
    Posts
    179
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index and match vertical and horizontal data

    Hi Matty

    Thank you very much for the formula - Great, it matches my exact requirement. However I do have a query - My Transaction Table is more than 1 million rows, in that case how do i change the range value.

    Also the Column A records will be more than 50,000 records matched with dates running for 90 days - Please advice - thanks

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,929
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Index and match vertical and horizontal data

    With a sample of your data run this macro.
    But excel is not so fast to work with a million records, maybe you should try Access:


    Code:
    Option Explicit
    Sub ID_Transaction()
        Dim sh1 As Worksheet, sh2 As Worksheet, rngID As Range
        Dim c As Range, r As Range, b As Range, f As Variant, cell As String
        
        Set sh1 = Sheets("Sheet1")
        Set sh2 = Sheets("Sheet2")
        
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Application.StatusBar = False
        Set rngID = sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
        For Each c In rngID
            Application.StatusBar = "Searching row: " & c.Row & " of: " & rngID.Count
            Set r = sh2.Columns("A")
            Set b = r.Find(c.Value, LookAt:=xlWhole, LookIn:=xlValues)
            If Not b Is Nothing Then
                cell = b.Address
                Do
                    f = Application.Match(b.Offset(, 1), sh1.Rows(1), 0)
                    If Not IsError(f) Then
                        sh1.Cells(c.Row, f).Value = b.Offset(0, 2)
                    End If
                    Set b = r.FindNext(b)
                Loop While Not b Is Nothing And b.Address <> cell
            End If
        Next
        
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        Application.StatusBar = False
            
        MsgBox "Done"
    End Sub
    Either way, let me know if you have any doubts
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,697
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index and match vertical and horizontal data

    Quote Originally Posted by Blessy Clara View Post
    Hi Matty

    Thank you very much for the formula - Great, it matches my exact requirement. However I do have a query - My Transaction Table is more than 1 million rows, in that case how do i change the range value.

    Also the Column A records will be more than 50,000 records matched with dates running for 90 days - Please advice - thanks
    That is a fairly big data set for Excel to cope with.

    In the absence of a proper database, e.g. SQL Server or Access, consider using Power Query instead. You could then carry out any data transformation there and pivot the data as required. And it will be quick, even on a data set of this size.

    Matty

  6. #6
    Board Regular
    Join Date
    Mar 2010
    Posts
    179
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index and match vertical and horizontal data

    Hi Matty Thank you for the suggestion
    Hi DanteAmor Thanks alot for the code


  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,929
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Index and match vertical and horizontal data

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •