SOLVED

New Contributor

# 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

5 Replies
best response confirmed by NFG2004 (New Contributor)
Solution

# Re: Merging data to import pollution values

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.

# Re: Merging data to import pollution values

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

# Re: Merging data to import pollution values

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!!

# Re: Merging data to import pollution values

Hi Kodipady, perfect - thank you so much - the formula was far more complicated than i expected and i was approaching is from eh wrong angle

Just what i needed, thanks again

Kind regards

# Re: Merging data to import pollution values

@NFG2004 , I am glad it helped!!