Table, extract content from a row, remove blanks and order in order A-Z

mstgier

New Member
Joined
May 28, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Dear Forum,
I am not new to Excel but until now didn't use it in a sophisticated way. I am stuck with a Table, where I am not able to get to my target.

What am I trying to do is the following in:

As you can see in the below picture, I did some experimenting before going back to my table and Google was of course my good old friend to help me find a solution ;)

Row A is my Example, where I try to get data out from. I found a formula, to remove the blanks and show them in Row C. The Formula I used is the following:

=IFERROR(INDEX($A$2:$A$17, SMALL(IF(LEN($A$2:$A$17)=0,"", ROW($A$2:$A$17)-MIN(ROW($A$2:$A$17))+1), ROW(A1))),"")

The next formula in Row D is removing the duplicates and is ordering it in alphabetical order:

=IFERROR(INDEX($C$2:$C$11, MATCH(SMALL(IF(COUNTIF($D$1:D1, $C$2:$C$11)=0, COUNTIF($C$2:$C$11, "<"&$C$2:$C$11), ""), 1), COUNTIF($C$2:$C$11, "<"&$C$2:$C$11), 0)),"")

Now comes the problem!

If we have a look at Row F, as you can see, I have my table called "Test" and the Row is called "Example_with_a_table" (This ridiculous name is only used as an example).

Is there a way, I can extract all the data from the Table and have the same result like in Row D? It doesn't matter if the solution is within the Table or not, as long as I do get the result.

Where I am struggling is, that with the result in Row D, I am referring to a range but in a Table, I am addressing the Table itself with the [@[Example with a table]],
therefore, I assume, I have to address my request in a different way but I have no clue how or if it is possible with the formulas I started, not sure how to change it
to read from the table.

May I challenge you?

Thank you,

Mike

Excel Formula.JPG
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
have you tried this? A1:A11 is a inserted table.
Book3
ABC
1ExampleExample
2aa
3bb
4c
5cd
6a
7
8d
9d
10
11b
Sheet1
Cell Formulas
RangeFormula
C2:C5C2=UNIQUE(FILTER(Table1[Example],Table1[Example]<>""))
Dynamic array formulas.
 
Upvote 0
That is faszinating and not that difficult ;) It works perfectly also for me...

Thanx a lot... I was not aware of Unique but will read about it now and try to understand...

Thanx a million (y)
 
Upvote 0
That is faszinating and not that difficult ;) It works perfectly also for me...

Thanx a lot... I was not aware of Unique but will read about it now and try to understand...

Thanx a million (y)
You are welcome~ :giggle: New functions in Office 365 are very useful, such as UNIQUE, SORT, FILTER, they are easy to use and understand. Have a look at these functions will save a lot of time.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top