Forum Discussion
Merging data to import pollution values
Good evening
The issue i am having is that i have a a pollution activity tracker that exports its data in 2 files, i file has user postilion data (so GPS coordinates) the other has my actual pollution data such as Nox, PM2.5 PM10 etc. The data is collected maybe every minute for pollution but every 4-5 minutes for GPS - so I have 2 tables where date and time do not match, I have tried VLOOKUP, INDEX and MATCH etc, but cannot import data - I have around 13,600 lines of measures data and around 5600 lines of GPS data as location not collected as often - as I want to map this i want to ignore any lines where i dont have a GPS location against that time - hope this makes sense
what I want to achieve is where the data and times match I want to import the corresponding data (measures data table) such as AQ, PM2.5 & NOX etc from the user data table onto the GPS data table. I would imagine this can be done but having searched first I have been unable to find a specific solution to this so would appreciate some expert help on correct formula to achieve please - Thank you
Just to clarify, you would like to see in GPS sheet only the rows where the Date field matches with that of Measures sheet. Is that correct?
Match/Countif/Vlookup formulae should be able to help. If you could upload a excel file with some realistic sample data, it will help the contributors here to give a specific solution.
5 Replies
Just to clarify, you would like to see in GPS sheet only the rows where the Date field matches with that of Measures sheet. Is that correct?
Match/Countif/Vlookup formulae should be able to help. If you could upload a excel file with some realistic sample data, it will help the contributors here to give a specific solution.
Hi Kodipady, thank you
Yes on the GPS sheet i would like to see the pollution data from the measures sheet if the date and time match. so if the Date and Time on the GPs sheet is found on the Measures sheet - the data values in col B:I for the matching line is copied into the matching line on the GPS sheet Col D:K - this way i have a sheet with of data values and GPS details so that i can import this to say Bing Maps or similar
I have added a file so hopefully this has uploaded,
thank you , this was useful.
Please check the following formula, you can paste this to row 2(column D to K) of GPS tab.
=IFERROR(VLOOKUP([@date],Table1,COLUMN()-2,FALSE),"")
If there is no date match in Measures tab, it will give a blank cell. now you can filter on column D for non-blank rows. . i have attached a version with these changes.
hope it helps!!
