Having extracted data that is copied and pasted into excel with a combined date and time field. I am using the following formulato separate the time and then using the formulaFormula:Please Login or Register to view this content.
Formula: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
Bookmarks