Model: GPT-4
Avatar of the person chatting

I have a row in Excel from UT109:ZU109 that contains either numbers of blank cells. I need to pull out only the numbers and put them into a list using a formula.

You can achieve this using a combination of the IF, SMALL, COLUMN, and INDEX functions in a new cell where you want to start listing the numbers. Let's say you want to start listing the numbers in cell A1.

  1. In cell A1, enter the following formula:
lessCopy code
=IFERROR(SMALL(IF(UT109:ZU109<>"",COLUMN(UT109:ZU109)-COLUMN(UT109)+1),ROW(A1)), "")

This formula uses the IF function to filter out blank cells, and then it uses the COLUMN function to create an array of the relative column positions of the non-blank cells. The SMALL function then returns the smallest value of this array based on the row number of A1. If there are no more numbers to extract, the IFERROR function will display an empty cell.

  1. Press Ctrl + Shift + Enter to make this an array formula. Excel will automatically surround the formula with curly braces {} to indicate that it's an array formula.

  2. Now, you'll need to copy the formula down to more rows in column A to accommodate all the numbers from the range UT109:ZU109. Simply click on cell A1, then drag the fill handle (small square in the bottom-right corner of the cell) down to fill as many rows as needed.

This will extract all the numbers from the given range (UT109:ZU109) and list them in column A starting from A1. If you want to start listing the numbers in a different cell, you'll need to adjust the ROW(A1) part of the formula accordingly. For example, if you want to start listing the numbers in cell B3, change ROW(A1) to ROW(B1).

Avatar of the person chatting

Great. One small change. I'd like to start in cell A1 but then copy to the right, rather than down.

Sure, you can do this by modifying the formula slightly to accommodate filling the formula to the right instead of down.

  1. In cell A1, enter the following formula:
lessCopy code
=IFERROR(SMALL(IF(UT109:ZU109<>"",COLUMN(UT109:ZU109)-COLUMN(UT109)+1),COLUMN(A1)),"")

In this modified formula, we changed the ROW(A1) part to COLUMN(A1), so the numbers will be pulled out as you copy the formula to the right.

  1. Press Ctrl + Shift + Enter to make this an array formula. Excel will automatically surround the formula with curly braces {} to indicate that it's an array formula.

  2. Now, you'll need to copy the formula to the right in row 1 to accommodate all the numbers from the range UT109:ZU109. Simply click on cell A1, then drag the fill handle (small square in the bottom-right corner of the cell) to the right to fill as many columns as needed.

This will extract all the numbers from the given range (UT109:ZU109) and list them in row 1 starting from cell A1.

Avatar of the person chatting

That didn't work

Apologies for the confusion. Here's an alternative method using the same formula, but with a slight modification to accommodate filling the formula to the right:

  1. In cell A1, enter the following formula:
lessCopy code
=IFERROR(SMALL(IF(UT109:ZU109<>"",COLUMN(UT109:ZU109)),COLUMN(A1)),"")
  1. Press Ctrl + Shift + Enter to make this an array formula. Excel will automatically surround the formula with curly braces {} to indicate that it's an array formula
Avatar of the person chatting

Let's try an entirely different logic. Again, in cells UT109:ZU109 I have either numbers or blank cells. I want to simply extract the numbers so that I can put them into a list.

I apologize for the confusion earlier. Here's another approach using the FILTER and TRANSPOSE functions. This method works in Excel for Microsoft 365 or Excel 2021 that supports dynamic array formulas.

  1. In cell A1, enter the following formula:
rubyCopy code
=TRANSPOSE(FILTER(UT109:ZU109,(UT109:ZU109<>"")))

This formula uses the FILTER function to extract non-blank cells from the range UT109:ZU109. The TRANSPOSE function then rearranges the output from a horizontal range to a vertical range.

  1. Press Enter.

As long as you're using a version of Excel that supports dynamic arrays, the numbers will automatically be extracted and listed starting from A1 and will expand vertically as needed.

If you want the list to be horizontal, you can simply remove the TRANSPOSE function:

rubyCopy code
=FILTER(UT109:ZU109,(UT109:ZU109<>""))

Press Enter and the extracted numbers will be listed horizontally starting from cell A1.

Avatar of the person chatting

Any alternative that works with earlier versions of Excel?

Yes, here's a solution using the INDEX, SMALL, and IF functions that should work with earlier versions of Excel. We will create a helper row to accommodate the column numbers of the non-empty cells in the range and then use the INDEX function to fetch the numbers.

  1. In an empty row, let's say row 110, enter the following formula in cell UT110 and then copy it to the right through cell ZU110:
lessCopy code
=IF(UT109<>"",COLUMN(UT109),"")

This will create a row with the column numbers of non-empty cells in the range UT109:ZU109.

  1. In cell A1, enter the following formula:
lessCopy code
=IFERROR(INDEX(UT109:ZU109,1,MATCH(SMALL(UT110:ZU110,COLUMN(A1)),UT110:ZU110,0)),"")

This formula will first find the smallest value in the helper row (UT110:ZU110) using the SMALL function. Then, it uses the MATCH function to find the relative position of that value within the helper row. Finally, the INDEX function retrieves the corresponding number from the original range (UT109:ZU109).

  1. Copy the formula in A1 to the right for as many columns as needed to list all the numbers in the range UT109:ZU109.

This method should work with earlier versions of Excel that do not support dynamic arrays.

Avatar of the person chatting

Put the helper row in 108

Sure, we can move the helper row to row 108. Follow these steps:

  1. In cell UT108, enter the following formula and then copy it to the right through cell ZU108:
lessCopy code
=IF(UT109<>"",COLUMN(UT109),"")

This will create a helper row with the column numbers of non-empty cells in the range UT109:ZU109.

  1. In cell A1, modify the formula to use the helper row in row 108:
lessCopy code
=IFERROR(INDEX(UT109:ZU109,1,MATCH(SMALL(UT108:ZU108,COLUMN(A1)),UT108:ZU108,0)),"")
  1. Copy the formula in A1 to the right for as many columns as needed to list all the numbers in the range UT109:ZU109.

By following these steps, you will have the helper row in row 108, and the extracted numbers will be listed horizontally starting from cell A1.

Avatar of the person chatting

Change A1 to I272

To start listing the numbers in cell I272, follow these steps:

  1. In cell UT108, enter the following formula and then copy it to the right through cell ZU108:
lessCopy code
=IF(UT109<>"",COLUMN(UT109),"")

This will create a helper row with the column numbers of non-empty cells in the range UT109:ZU109.

  1. In cell I272, modify the formula to use the helper row in row 108:
lessCopy code
=IFERROR(INDEX(UT109:ZU109,1,MATCH(SMALL(UT108:ZU108,COLUMN(I272)-COLUMN(I272)+1),UT108:ZU108,0)),"")
  1. Copy the formula in I272 to the right for as many columns as needed to list all the numbers in the range UT109:ZU109.

By following these steps, you will have the helper row in row 108, and the extracted numbers will be listed horizontally starting from cell I272.

Avatar of the person chatting

Make so that I copy I272 down in a vertical list

To create a vertical list in column I starting from cell I272, modify the formula as follows:

  1. In cell UT108, enter the following formula and then copy it to the right through cell ZU108:
lessCopy code
=IF(UT109<>"",COLUMN(UT109),"")

This will create a helper row with the column numbers of non-empty cells in the range UT109:ZU109.

  1. In cell I272, modify the formula to use the helper row in row 108:
lessCopy code
=IFERROR(INDEX(UT109:ZU109,1,MATCH(SMALL(UT108:ZU108,ROW(I272)-ROW(I272)+1),UT108:ZU108,0)),"")

Notice that we changed the COLUMN function to ROW, and we updated the cell references from COLUMN(I272)-COLUMN(I272)+1 to ROW(I272)-ROW(I272)+1.

  1. Copy the formula in I272 down for as many rows as needed to list all the numbers in the range UT109:ZU109.

By following these steps, you will have the helper row in row 108, and the extracted numbers will be listed vertically starting from cell I272.

Avatar of the person chatting

Hmmm.. that doesn't seem to work. It seems because the reference ranges aren't set to be absolute, when I copy down the range moves.

Apologies for the oversight. You are correct, we need to use absolute references for the ranges. Here's the corrected formula for cell I272:

  1. In cell UT108, enter the following formula and then copy it to the right through cell ZU108:
lessCopy code
=IF(UT109<>"",COLUMN(UT109),"")

This will create a helper row with the column numbers of non-empty cells in the range UT109:ZU109.

  1. In cell I272, modify the formula to use absolute references for the helper row in row 108 and the range UT109:ZU109:
swiftCopy code
=IFERROR(INDEX($UT$109:$ZU$109,1,MATCH(SMALL($UT$108:$ZU$108,ROW(I272)-ROW($I$272)+1),$UT$108:$ZU$108,0)),"")

Notice that we added the $ symbol to make the range references absolute so that they don't change when the formula is copied down.

  1. Copy the formula in I272 down for as many rows as needed to list all the numbers in the range UT109:ZU109.

By following these steps, you will have the helper row in row 108, and the extracted numbers will be listed vertically starting from cell I272.

Avatar of the person chatting

That worked!

I'm glad the solution worked for you! If you have any more questions or need further assistance with Excel or any other topic, please don't hesitate to ask.