Home

Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-659898%22%20slang%3D%22en-US%22%3EFormula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-659898%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20any%20one%20help%20me%20determine%20the%20formula%20needed%20to%20accomplish%20this%20task%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20columns%20of%20numbers%20on%20the%20same%20worksheet.%20%26nbsp%3BColumn%201%20is%20the%20beginning%20range%20of%20a%207%20digit%20number%20and%20Column%202%20is%20the%20ending%207%20digit%20range.%20%26nbsp%3BSome%20of%20the%20ranges%20are%20a%201%20to%201%20match%20(example%3A%20Beginning%20range%20is%201234567%20and%20the%20ending%20range%20is%201234567).%20%26nbsp%3BHowever%2C%20most%20of%20the%20ranges%20are%20much%20larger%20(example%3A%20Beginning%20range%20is%201234567%20and%20Ending%20Range%20is%20123767).%20%26nbsp%3BI'm%20looking%20for%20a%20formula%20that%20will%20automatically%20expand%20this%20range%20and%20list%20all%20consecutive%20numbers%20between%20the%20beginning%20and%20ending%20range%20and%20if%20the%20number%20is%20the%20same%20in%20the%20beginning%20%26amp%3B%20ending%20range%2C%20then%20I%20need%20for%20that%20number%20to%20be%20listed%20in%20a%20column.%20%26nbsp%3BI%20have%201622%20rows%20with%20different%20ranges%20that%20I%20need%20to%20apply%20this%20formula%20to.%20%26nbsp%3BMy%20ultimate%20goal%20is%20to%20list%20all%20numbers%20in%20a%20single%20column%20to%20compare%20them%20to%20a%20master%20list%20of%206000%20numbers%20to%20see%20what%20is%20missing.%20%26nbsp%3BAny%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-659898%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-660112%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-660112%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F352385%22%20target%3D%22_blank%22%3E%40Todd_Winkle%3C%2FA%3E%26nbsp%3B%2C%20that%20could%20be%20done%20with%20Power%20Query%20-%20query%20your%20columns%20with%20Start%2FEnd%2C%20generate%20lists%20for%20ranges%20and%20expand%20them%20to%20new%20column.%20That%20is%20in%20attached%20file.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20that%20you%20may%20merge%20that%20query%20with%20your%20master%20list%20to%20check%20what%20is%20missed%20and%20land%20final%20result%20into%20Excel%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-661343%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-661343%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20help%26nbsp%3B%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.%20%26nbsp%3BAfter%20figuring%20out%20that%20I%20had%20to%20download%20the%20Power%20query%20add-in%2C%20i%20was%20able%20to%20do%20what%20I%20needed%20to%20quickly.%20%26nbsp%3BThis%20was%20a%20great%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Todd_Winkle
New Contributor

Can any one help me determine the formula needed to accomplish this task?

 

I have two columns of numbers on the same worksheet.  Column 1 is the beginning range of a 7 digit number and Column 2 is the ending 7 digit range.  Some of the ranges are a 1 to 1 match (example: Beginning range is 1234567 and the ending range is 1234567).  However, most of the ranges are much larger (example: Beginning range is 1234567 and Ending Range is 123767).  I'm looking for a formula that will automatically expand this range and list all consecutive numbers between the beginning and ending range and if the number is the same in the beginning & ending range, then I need for that number to be listed in a column.  I have 1622 rows with different ranges that I need to apply this formula to.  My ultimate goal is to list all numbers in a single column to compare them to a master list of 6000 numbers to see what is missing.  Any suggestions?

2 Replies

@Todd_Winkle , that could be done with Power Query - query your columns with Start/End, generate lists for ranges and expand them to new column. That is in attached file.

 

After that you may merge that query with your master list to check what is missed and land final result into Excel sheet.

Thanks for the help @Sergei Baklan.  After figuring out that I had to download the Power query add-in, i was able to do what I needed to quickly.  This was a great help!

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies