+ Reply to Thread
Results 1 to 3 of 3

Match/Lookup?...No unique identifier

  1. #1
    Registered User
    Join Date
    08-10-2012
    Location
    Glasgow
    MS-Off Ver
    Excel 2007, 2010
    Posts
    2

    Match/Lookup?...No unique identifier

    Hi,

    I hope someone can help with this....

    I have 2 reports coming out of 2 seperate systems. To make things simpler I have put an example of report 1 on sheet 1 and report 2 on sheet 2.

    On sheet 2 column G, I need to populate the 'Interaction ID' from sheet 1 column A. This way I'll be able to tie together the two reports.

    Here's what I need it to check;
    1. Sheet 1 column C is the time a call is taken and column H is the time the call ended. Sheet 2 column F is the time the sale was processed so this time on sheet 2 column F will fall in between the two times on sheet 1 columns C & H.
    2. The agent has to be looked up as there are multiple agents taking calls so there will be multiple times that are similar.
    3. The date also has to match as the agent could take a call at 9am on 1st November and 9am on 2nd November and they will be on the same report, so I need it to match the date too.

    If this can be done then is should return the interaction_ID from sheet 1 column A.

    PLEASE NOTE: Not all formulas in Sheet 2 column G will return an Interaction_ID if no sale was made on the call. e.g. Cell G1, G2 in sheet 2 should return blank and cell G3 should return interaction_ID 67383144.

    I hope that all makes sense...... :S

    Thanks in advance!

    Scott.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-16-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: Match/Lookup?...No unique identifier

    Hey

    I dont know how to attach the sheets with reply so am pasting the formula to be put in sheet 2 cell G2, and copy all the way below:

    =SUMPRODUCT(('Sheet 1'!$B$2:$B$14='Sheet 2'!B2)*('Sheet 1'!$C$2:$C$14<'Sheet 2'!F2)*('Sheet 1'!$H$2:$H$14>'Sheet 2'!F2)*('Sheet 1'!$F$2:$F$14='Sheet 2'!C2)*('Sheet 1'!$G$2:$G$14="Sale")*('Sheet 1'!$A$2:$A$14))

    Hope it helps!
    #
    cheers
    BI.

  3. #3
    Registered User
    Join Date
    08-10-2012
    Location
    Glasgow
    MS-Off Ver
    Excel 2007, 2010
    Posts
    2

    Re: Match/Lookup?...No unique identifier

    Hi BiIntel,

    Thanks for this! I was messing around with sumproduct and just couldn't quite get it to work.

    This works perfectly.

    Thanks for your help and the speedy reply.

    Scott.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1