SOLVED
Home

Merging data to import pollution values

%3CLINGO-SUB%20id%3D%22lingo-sub-782407%22%20slang%3D%22en-US%22%3EMerging%20data%20to%20import%20pollution%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-782407%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20evening%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20i%20am%20having%20is%20that%20i%20have%20a%20a%20pollution%20activity%20tracker%20that%20exports%20its%20data%20in%202%20files%2C%20i%20file%20has%20user%20postilion%20data%20(so%20GPS%20coordinates)%20the%20other%20has%20my%20actual%20pollution%20data%20such%20as%20Nox%2C%20PM2.5%20PM10%20etc.%20The%20data%20is%20collected%20maybe%20every%20minute%20for%20pollution%20but%20every%204-5%20minutes%20for%20GPS%20-%20so%20I%20have%202%20tables%20where%20date%20and%20time%20do%20not%20match%2C%20I%20have%20tried%20VLOOKUP%2C%20INDEX%20and%20MATCH%20etc%2C%20but%20cannot%20import%20data%20-%20I%20have%20around%2013%2C600%20lines%20of%20measures%20data%20and%20around%205600%20lines%20of%20GPS%20data%20as%20location%20not%20collected%20as%20often%20-%20as%20I%20want%20to%20map%20this%20i%20want%20to%20ignore%20any%20lines%20where%20i%20dont%20have%20a%20GPS%20location%20against%20that%20time%20-%20hope%20this%20makes%20sense%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhat%20I%20want%20to%20achieve%26nbsp%3B%20is%20where%20the%20data%20and%20times%20match%20I%20want%20to%20import%20the%20corresponding%20data%20(measures%20data%20table)%20such%20as%20AQ%2C%20PM2.5%20%26amp%3B%20NOX%20etc%20from%20the%20user%20data%20table%20onto%20the%20GPS%20data%20table.%20I%20would%20imagine%20this%20can%20be%20done%20but%20having%20searched%20first%20I%20have%20been%20unable%20to%20find%20a%20specific%20solution%20to%20this%20so%20would%20appreciate%20some%20expert%20help%20on%20correct%20formula%20to%20achieve%20please%20-%20Thank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125149i8D4C4A1D38A37679%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screenshot%202019-07-31%2021.38.48.png%22%20title%3D%22Screenshot%202019-07-31%2021.38.48.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125150iA5B164F9099762F4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screenshot%202019-07-31%2021.36.22.png%22%20title%3D%22Screenshot%202019-07-31%2021.36.22.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-782407%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-782994%22%20slang%3D%22en-US%22%3ERe%3A%20Merging%20data%20to%20import%20pollution%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-782994%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385252%22%20target%3D%22_blank%22%3E%40NFG2004%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20to%20clarify%2C%20you%20would%20like%20to%20see%20in%20GPS%20sheet%20only%20the%20rows%20where%20the%20Date%20field%20matches%20with%20that%20of%20Measures%20sheet.%20Is%20that%20correct%3F%26nbsp%3B%3C%2FP%3E%3CP%3EMatch%2FCountif%2FVlookup%20formulae%20should%20be%20able%20to%20help.%26nbsp%3B%20If%20you%20could%20upload%20a%20excel%20file%20with%20some%20realistic%20sample%20data%2C%20it%20will%20help%20the%20contributors%20here%20to%20give%20a%20specific%20solution.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-783361%22%20slang%3D%22en-US%22%3ERe%3A%20Merging%20data%20to%20import%20pollution%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-783361%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Kodipady%2C%20thank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20on%20the%20GPS%20sheet%20i%20would%20like%20to%20see%20the%20pollution%20data%20from%20the%20measures%20sheet%20if%20the%20date%20and%20time%20match.%26nbsp%3B%20so%20if%20the%20Date%20and%20Time%20on%20the%20GPs%20sheet%20is%20found%20on%20the%20Measures%20sheet%20-%20the%20data%20values%20in%20col%20B%3AI%20for%20the%20matching%20line%20is%20copied%20into%20the%20matching%20line%20on%20the%20GPS%20sheet%20Col%20D%3AK%26nbsp%3B%20-%20this%20way%20i%20have%20a%20sheet%20with%20of%20data%20values%20and%20GPS%20details%20so%20that%20i%20can%20import%20this%20to%20say%20Bing%20Maps%20or%20similar%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20added%20a%20file%20so%20hopefully%20this%20has%20uploaded%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-783589%22%20slang%3D%22en-US%22%3ERe%3A%20Merging%20data%20to%20import%20pollution%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-783589%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385252%22%20target%3D%22_blank%22%3E%40NFG2004%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20%2C%20this%20was%20useful.%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20check%20the%20following%20formula%2C%20you%20can%20paste%20this%20to%20row%202(column%20D%20to%20K)%26nbsp%3B%20of%20GPS%20tab.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(VLOOKUP(%5B%40date%5D%2CTable1%2CCOLUMN()-2%2CFALSE)%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EIf%20there%20is%20no%20date%20match%20in%20Measures%20tab%2C%20it%20will%20give%20a%20blank%20cell.%26nbsp%3B%20now%20you%20can%20filter%20on%20column%20D%20for%20non-blank%20rows.%26nbsp%3B%20.%20i%20have%20attached%20a%20version%20with%20these%20changes.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3Ehope%20it%20helps!!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-783653%22%20slang%3D%22en-US%22%3ERe%3A%20Merging%20data%20to%20import%20pollution%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-783653%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Kodipady%2C%20perfect%20-%20thank%20you%20so%20much%20-%20the%20formula%20was%20far%20more%20complicated%20than%20i%20expected%20and%20i%20was%20approaching%20is%20from%20eh%20wrong%20angle%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20what%20i%20needed%2C%20thanks%20again%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-783928%22%20slang%3D%22en-US%22%3ERe%3A%20Merging%20data%20to%20import%20pollution%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-783928%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385252%22%20target%3D%22_blank%22%3E%40NFG2004%3C%2FA%3E%26nbsp%3B%2C%20I%20am%20glad%20it%20helped!!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
NFG2004
New Contributor

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

 

Screenshot 2019-07-31 21.38.48.pngScreenshot 2019-07-31 21.36.22.png

 

5 Replies
Solution

@NFG2004 

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.   

@Kodipady 

 

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, 

 

 

@NFG2004 

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

@Kodipady 

 

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

@NFG2004 , I am glad it helped!! 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies