matching data from 2 sheets based on date

I have a spreadsheet with 2 sheets (one named master, one named logs). In master, I have created a new column called "Logs" (column I) , where I want to import some data from the logs sheet. In the master spreadsheet column A is called date, and in the logs spreadsheet, column J is also called date, this will be key for the join/match. Also in the logs sheet is a column called "transaction time", which is format dd:mm:yy hh:ss. there are a number of transaction times per date! I wanted to get all the various transaction dates per date into one string of text delimited by ; , i.e. everything on 21/08/2015 in a fomat "21/08/2015 07:00; 21/08/2015 07:35; 21/08/2015 08:35"

and then copy that string of text into the "logs" column in the master spreadsheet!

any pointers?
LVL 3
pma111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pma111Author Commented:
the only other option, rather than merging the various transaction dates/.times into one string, is to import them into a new cell in master (in a vertical way, i.e. A2, B2, C2, D2 etc, the only problem there is there could be 1 or 20 transactions per each individual date, there is no set number of transaction per date.
0
Saurabh Singh TeotiaCommented:
You can create a additional column which is let's say column-K and do something like this in that column...

=text(j2,"mm/dd/yyyy")*1

And then you can drag this...and then you can create pivot table over it which will do what you are looking for as it will group all the dates...

Saurabh...
0
pma111Author Commented:
I wasnt trying to group the dates into one string of text, I want to essentially merge the text in column B (transcation dates), then import that string of text into column I in the master spreadsheet. I dont want a pivot table for the type of analysis required.

So the theory is

step 1, create a long string of text for each unique date into a new column, in a ; delimeted format, i.e. in column K of the logs sheet..
step 2, where date = date in master and logs, copy over the new string of text from step 1 into column  I in master. so the value of column K to be copied over in column I in master.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Saurabh Singh TeotiaCommented:
I'm confused about what you are trying to do..Can you post sample worksheet so that i can understand what you are doing..??

Saurabh...
0
pma111Author Commented:
if we focus on step 1 which is the tricky part...

in sheet "logs" is column B which is a set of dates/times, i.e.

2108/2015 07:00
21/08/2015 07:30
21/08/2015 08:00
22/08/2015 13:00
22.08/2015 13:45
23/08/2015 09:35
23/08/2015 10:15
23/08/2015 10:45

What I need to do, per date, is get a string of text of all these dates and times for copying into another sheet. so from the above, the formula/process should be returning into a single cell, the following text, as you can see grouped by date!:

"21/08/2015 07:00; 21/08/2015 07;30; 21/08/2015 08:00"
"22/08/2015 13:00; 22/08/2015 13:45"
"23/08/2015 09;35; 23/08/2015 10:15; 23/08/2015 10:45"
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@pma111

As you can see that out expert Saurabh Singh Teotia requested you to upload a sample workbook in order to help you but it seems you want us to make the dummy workbook for you and then create a solution for you. Sometime only description is not enough when the solution involves some complex steps.

So if you want to get a proper help, make a dummy workbook, populate some dummy data in the target columns  and in target sheets (of course remove data from all other column) and upload it here.
Please don't forget to remove any sensitive information also.
0
Saurabh Singh TeotiaCommented:
I'm assuming this is what you are looking for..

Saurabh...
Data.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pma111Author Commented:
Great!

That covers step 1.

Now I have a column in the logs sheet which I have titled "logs history" (column D). What I now need to do, is for where the date in column D = the same date listed in sheet "master" column A, is to copy all this string of text into column I of the master spreadsheet.

So if master.date = the date in logs.logs.history, then in master.I1 copy over the long string of data from column history in logs.
0
Saurabh Singh TeotiaCommented:
Pm111,

Their you go..Your step-2...

Saurabh...
Data.xlsx
0
pma111Author Commented:
thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.