+ Reply to Thread
Results 1 to 7 of 7

Require Help - Require Formula for Multiple values in both columns

  1. #1
    Registered User
    Join Date
    01-11-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    3

    Require Help - Require Formula for Multiple values in both columns

    Friends,

    Need help here urgently, I have a data values in two seperate sheet showing IN and OUTt date, time and sku's.

    Issue is there are multiple IN and OUT time, date for a single sku.

    I have tried several vlook up array formulas but unable to get through what is required.

    I want a formula which will capture ococcurence of each repeating sku against its IN and OUT date and time, as i have to find out which sku is still "IN".

    Attached sample data, help required asap.


    Warm Regards,
    Kashyap Rodge
    Attached Files Attached Files

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Require Help - Require Formula for Multiple values in both columns

    Hello,

    it might help if you could mock up the desired result and explain the logic.

  3. #3
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Require Help - Require Formula for Multiple values in both columns

    try this array formula

    =IF(MAX(IF($B$2:$B$1188=D2,$A$2:$A$1188,""))>MAX(IF(Outbound!$B$2:$B$1026=Inbound!D2,Outbound!$A$2:$A$1026,"")),"in","")

    it needs to be confirmed with Ctrl-Shift-Enter.

    see attached.

    cheers,
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-11-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Require Help - Require Formula for Multiple values in both columns

    Hi teyln,

    Thanks for quick response the formula works, however i cannot mock up the sku, since the data sheet will keep on updating day by day.
    How can i use above formula to arrive solution.

    regards,
    kashyap

  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Require Help - Require Formula for Multiple values in both columns

    The array formula will be slow enough with a large dataset.

    You could use a reference to the SKU in column A instead of the SKU in column D and copy the formula down all the way, but depending on how many rows of data you have, this may take a long time to recalculate.

    You could create a pivot table that will generate a list of unique SKUs. That's what I did to generate the list in column D. Then use the array formula side by side with the pivot table, referencing the SKU from the pivot table. Use a dynamic range to populate the pivot table and fill the array formula down as far as required.

  6. #6
    Registered User
    Join Date
    01-11-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Require Help - Require Formula for Multiple values in both columns

    can we avoid doing pivot and put formula against to available data in sheet inbound..

  7. #7
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Require Help - Require Formula for Multiple values in both columns

    Yes you can. Just change the reference in the formula as I explained above.

    Be prepared for a long wait while the spreadsheet calculates.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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