Duplicates with different values in another column

%3CLINGO-SUB%20id%3D%22lingo-sub-2819576%22%20slang%3D%22en-US%22%3EDuplicates%20with%20different%20values%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2819576%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20Excel%20365.%26nbsp%3B%20I%20removed%20a%20lot%20of%20columns%20and%20changed%20the%20values%20for%20privacy.%26nbsp%3B%20I%20have%20a%20list%20of%26nbsp%3B%20numbers%20(column%20A)%20and%20many%20of%20those%20numbers%20occur%20more%20than%20once%20in%20the%20file%20(I%20highlighted%20duplicate%20values).%26nbsp%3B%20I%20created%20a%20distinct%20number%20list%20in%20column%20E%20using%20the%20advance%20filter.%26nbsp%3B%20I%20am%20trying%20to%20see%20if%20a%20number%20has%20a%20qty%20in%20column%20B.%26nbsp%3B%20A%20number%20can%20have%20multiple%20lines%20without%20a%20qty%20but%20as%20long%20as%20it%20has%20one%20line%20with%20it%20is%20good.%26nbsp%3B%20I%20know%20I%20can't%20use%20a%20vlookup%20because%20I%20need%20the%20formula%20to%20look%20at%20all%20the%20rows%20the%20number%20has%20to%20determine%20if%20a%20number%20has%20at%20least%20one%20row%20with%20a%20qty.%26nbsp%3B%20I%20am%20stumped%20and%20looking%20for%20ideas.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2819576%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2819649%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicates%20with%20different%20values%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2819649%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1177608%22%20target%3D%22_blank%22%3E%40ALFischer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20221px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F315699i8A8167A0F75D3EAA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eunique%20values%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DUNIQUE(A2%3AA135)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Esum%20of%20qty%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DSUMIF(A2%3AA135%2C%20H2%23%2C%20B2%3AB135)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2822085%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicates%20with%20different%20values%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2822085%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bthank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi everyone, 

 

I am using Excel 365.  I removed a lot of columns and changed the values for privacy.  I have a list of  numbers (column A) and many of those numbers occur more than once in the file (I highlighted duplicate values).  I created a distinct number list in column E using the advance filter.  I am trying to see if a number has a qty in column B.  A number can have multiple lines without a qty but as long as it has one line with it is good.  I know I can't use a vlookup because I need the formula to look at all the rows the number has to determine if a number has at least one row with a qty.  I am stumped and looking for ideas.  

3 Replies

@ALFischer 

As here

image.png

unique values

=UNIQUE(A2:A135)

sum of qty

=SUMIF(A2:A135, H2#, B2:B135)