Excel - List out all the numbers within a given range

[klitton7]

I need a way to list all the numbers between each range of numbers in columns H and I.
It would also be great to have the associated information in the other columns appear with each entry (or at least column D)

Thank you!

 

[Logit]

Does your range refer to Col G or Col C ?

 

[klitton7]

Sorry about that - it's all the numbers between columns H & I

 

[Logit]

Could you post a copy of your workbook (don't include confidential data) to a cloud site then provide the download link here ?

 

[Logit]

Also there are two easy methods to accomplish your goal.

#1: You could sort Col H ascending and when asked "Expand the selection" click OK.

#2: You can create a FILTER ascending with expand the selection.

 

[alansidman]

Additionally, in power query, you do a list between for each record. But we will need to see a file and not a picture as we cannot manipulate data in a picture.

 

[klitton7]

Could you post a copy of your workbook (don't include confidential data) to a cloud site then provide the download link here ?

Dropbox

www.dropbox.com

(see if this link works)

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table_Query_from_MAGNOLIA4789"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sStmt_cd", type text}, {"sFormat_cd", Int64.Type}, {"iGroup", Int64.Type}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"iLine", Int64.Type}, {"sFromAcct_cd", Int64.Type}, {"sThruAcct_cd", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {[sFromAcct_cd]..[sThruAcct_cd]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

here is your file with the update

Sage - Format Groups.xlsx | Powered by Box

app.box.com

 

[klitton7]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table_Query_from_MAGNOLIA4789"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sStmt_cd", type text}, {"sFormat_cd", Int64.Type}, {"iGroup", Int64.Type}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"iLine", Int64.Type}, {"sFromAcct_cd", Int64.Type}, {"sThruAcct_cd", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {[sFromAcct_cd]..[sThruAcct_cd]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

here is your file with the update

Sage - Format Groups.xlsx | Powered by Box

app.box.com

That looks great - I'm not familiar with power query - I'll have to check it out.
Would you mind applying it to tabs named 11 & 12 as well?

 

[alansidman]

While I am working on 11 and 12, here are some links you will find helpful.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions