Comparing and getting data between two sheets with multiple instances of identifiers

[LittelT85]

Hi,

I would need to compare the sampling dates between the two tables. Both tables have sampling dates for samples taken at different farms. Sample1 and Sample2 refer to different kind of samples taken. I would like to know how much time there is between the different sampling dates for a given farm. For example, in table 1, from farm1, three samples (sample1_1, sample1_2, sample1_3) were taken at two different time points. I would like to know 1) whether farm1 was also sampled in another sampling scheme (table2), and 2) how much time was there between the sampling points. In table2, there might be several sampling occasions for one farm and these all should be included in the comparison.

I have tried using the FILTER function, but clearly some more advanced is needed here. Does anyone have any ideas? I have attached a sample file.Example2.xlsx

Any advice or ideas are more than welcome! Perhaps this is something that is not attainable with Excel.

Thanks!

[sandy666]

is that what you want?

Farm_ID	Days.1	Days.2	Days.3	Days.4
Farm1	159	94	-244	-371
Farm1	159	94	-244	-371
Farm1	251	186	-152	-279
Farm1	281	216	-122	-249
Farm2	792	-176
Farm3	732	215
Farm4	727	606	554	493
Farm4	727	606	554	493
Farm4	790	669	617	556
Farm4	790	669	617	556
Farm5	180
Farm6	144
Farm7	240	137
Farm8
Farm9
Farm9
Farm10	729
Farm11	729
Farm11	769
Farm12

with Power Query

[windknife]

Clean all expected results.

E2

Please Login or Register  to view this content.

[LittelT85]

Hi sandy666 and windknife,

Thank so much for your help! I have to consult my colleague about these solutions, but I think either would work for our data.

[windknife]

You are welcome.
-----------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.

[sandy666]

You are welcome

---

if the problem is solved, make me happy and hit Add Reputation (bottom left corner next to the post that was helpful)