-2

I am using this formula in Excel 2016 so it doesn't have the sort function.

=IFERROR(INDEX($F$41:$F$500, MATCH(SMALL(IF(COUNTIF($J$1:J1,$F$41:$F$500)=0,COUNTIF($F$41:$F$500,"<"&$F$41:$F$500),""),ROW(1:1)),COUNTIF($F$41:$F$500,"<"&$F$41:$F$500),0)),"")

Wanting this to begin in Cell J2 instead of J22. I believe it is because the formula I have needs to exclude blank cells and is not. Also, for some odd reason the formula is leaving some of the data out. I have in column B2:B35 what J2:J35 should look like. Highlighted are what should also be showing in J2:J35 formula.

Here is a link to it. https://1drv.ms/x/s!AgYDS7saPf0DhsYpr01N9vmZhFUtCQ?e=n7wz6Z

As you can see in these images, the referenced cells are not sorted and have blanks. I am needing the formula to sort and remove blanks. Thanks!

Formula

References

Improve this question
5
  • Don't see a response from toyota Supra. Commented Sep 26, 2024 at 11:32
  • Supra edited your post, doesn't seem to have commented. Users are unlikely to download a file for security reasons. You're better off using images or markdown tables to show the structure of your data/sheets. Try to explain in your edit what it looks like and what you expect it to look like, even if it's a simple example. This will increase your chances of getting a solution. Commented Sep 26, 2024 at 17:30
  • The images don't provide much insight by themselves. First we don't see the rows in one, no columns in the other so we can't determine what your formula is looking at. Additionally, we're seeing what you don't want, but want to see what you do want. So sorted... but it looks like it's a lookup from the amount column to pull text, but unclear how it's pulled. Commented Sep 26, 2024 at 18:21
  • I just tried to provide a better image and edited my request to hopefully make more sense. Thank you for your patience and help. Commented Sep 26, 2024 at 18:40
  • I have explained in my edit what it looks like and what you expect it to look like and have provided images and the formula for cell J2. There is nothing more I can provide. Sure hope someone can provide me with some help with this. I need this before Monday. Commented Sep 27, 2024 at 14:20

1 Answer 1

Reset to default
0

The formula didn't work as expected as the method is for input with no duplicates.

Getting a sorted list of unique items is easy in Excel for Microsoft 365: =SORT(UNIQUE(TOCOL(_0_unsorted_input,1)))

Since these functions are not available in Excel 2016, a few interim steps are required. Here's one way to do it:

  • Eliminate duplicates:

    • Index trimmed input

    • Use FREQUENCY to select unique values

  • Alphabetize the trimmed, unique input using COUNTIF

  • Remove blanks

Removing blanks as second steps could work, but it requires manually selecting interim range - so I have chosen this order.

Used Name Manager

Defining names for ranges and formulas makes it easy to follow and troubleshoot the steps. So defined the following names (Formulas > Name Manager > New)

Name Refers to:
_0_unsorted_input =Sheet1!$B$4:$B$500 replace with your input range
_0i_input_index =ROW(_0_unsorted_input) - (ROW(INDEX(_0_unsorted_input, 1, 1))) + 1
_1_trimmed_input =TRIM(_0_unsorted_input)
_2_matched =MATCH(_1_trimmed_input,_1_trimmed_input,0)
_3_freq =FREQUENCY(_2_matched, _2_matched)
_4_uniq =INDEX(_1_trimmed_input,_5_uniq_index)
_4_uniq_range =Sheet1!$D$4:$D$500 a range with same number of rows as input
_5_uniq_index =IF((_0_unsorted_input <> """") * (_3_freq),_2_matched,"""")
_6_countif_less_than =IF((_4_uniq_range = "") + ISERROR(_4_uniq_range),"",
COUNTIF(_4_uniq_range,"<" & _4_uniq_range))
_7_sorted_index =SMALL(IF(ISERROR(_4_uniq_range),"",_6_countif_less_than),_0i_input_index)
_8_interim_sort =INDEX(_4_uniq_range,MATCH(_7_sorted_index,_6_countif_less_than,0))
_8_interim_sort_range =Sheet1!$E$4:$E$500 a range with same number of rows as input
_9_sorted =INDEX(_8_interim_sort_range,1,1):INDEX(_8_interim_sort_range,MATCH(TRUE,ISERROR(_8_interim_sort_range),0)-1,1)

Steps using COUNTIF and the last step to remove blanks require a range as input. So enter the formulas for these steps and one for the final result, here's how they are entered for the example:

These are array formulas and need to be entered with Control + Shift + Enter

Cell Formula
D4, with D4:D500 selected = _4_uniq
E4, with E4:E500 selected = _8_interim_sort
F4 = _9_sorted

I have tested this under Excel for Microsoft 365 - will test under Excel 2016 tomorrow.

Name Manager

Result

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Does this mean I need to create 9 named ranges? I have a total of 9 sheets to apply this to inside of a single workbook. Just wondering if you think that is excessive? That will be 9x9=81 named ranges.
No, you don't need to create 9 ranges. You can use the one sheet for results as shown here. But you need to enter 9 named formulas as shown above. Then in the "results" sheet, for the _0_unsorted_input, specify your ranges from other sheets. How many rows do you have combined in all the sheets? I'm on the road today, so I'll follow up later in the day.
I appreciate your effort. I cannot wrap my head around this. Seems to be overly complex to just sort and remove duplicate text values.
Are you trying to get data from all 9 sheets and sort and remove blanks? Is it a one time task? If you could provide at least 2-3 sample input ranges (Sheet1!A40:A400 etc.) I could provide you detailed steps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.