+ Reply to Thread
Results 1 to 3 of 3

Index and match formula not pulling data through correctly when using time field

  1. #1
    Registered User
    Join Date
    07-04-2012
    Location
    crewe, england
    MS-Off Ver
    2013
    Posts
    36

    Index and match formula not pulling data through correctly when using time field

    Having extracted data that is copied and pasted into excel with a combined date and time field. I am using the following formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    to separate the time and then using the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I am trying to extract data that matches to the warehouse and a specific time as laid out in the ExportFile sheet. I have thought about just pivoting the raw data to provide what I need but as this is to paste data into another sheet rather than manually key the information in. It would be messy using a pivot as not all warehouse areas will have data within them on a given day, so a messy copy and paste. The structure in the ExportFile sheet enable the range B2:T25 to be copied and paste values to another simply where there is data or not.
    Initially my formula’s appeared to be working correctly however having tried pulling the information for different dates it’s clear that there is some kind of issue as the Invoices Lines total 37,269 on the RawData sheet which is copy and pasted data from external source; while the ExportFile for the transposed data via the above formula totals 36432. A difference of 837 the lines of data not pulled through are as follows:


    I have checked the format etc. on both column A in both sheets and I can’t find a difference’ but the main issue seems to be on hours 01:00:00 to: 07:00:00
    I attach a copy of my workbook in the hope that someone can help identify why this is happening and help me solve the problem

    Many thanks

    Mark
    Attached Files Attached Files

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Index and match formula not pulling data through correctly when using time field

    in RawData sheet a2=TIME(HOUR(D2),,) use this and copy towards down
    and in ExportFile sheet a2=TIME(ROWS(A$2:A2)-1,,) use this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Registered User
    Join Date
    07-04-2012
    Location
    crewe, england
    MS-Off Ver
    2013
    Posts
    36

    Re: Index and match formula not pulling data through correctly when using time field

    Siva

    Many thanks perfect solution

    Regards

    Mark

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Index match formula is not pulling the correct data
    By thomashasler in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-20-2015, 03:14 PM
  2. [SOLVED] MATCH/INDEX Formula Help - Pulling Data
    By mlbdc2012 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-03-2015, 01:43 AM
  3. Index Match formula does not look-up/display data all the time.
    By iceandfire in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-18-2014, 08:46 PM
  4. Index-Match Formula is pulling incorrect value
    By wghalim in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-17-2014, 08:16 AM
  5. Replies: 1
    Last Post: 05-08-2014, 10:21 AM
  6. [SOLVED] Help-> Index+Match formula pulling wrong values
    By sashafierce in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-29-2013, 07:13 PM
  7. Index/Match formula not pulling through desired results
    By Climaxgp in forum Excel General
    Replies: 0
    Last Post: 08-02-2010, 08:14 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1