Formula to get unique list of values from multiple columns in a table

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

Thanks CARACALLA, though I'm confused, as I've posted an Excel workbook, not a pic. P

That's brilliant, wk9128. I just had to wrap that formula in the UNIQUE and SORT functions to get exactly what I needed. That makes it look so easy-peasy! Many thanks.

@paulma1960 You're Welcome. Glad to help . Thank You for the feedback


If you finally get a solution please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".
- Please click on the *Add Reputation button at the bottom left. you can thank those who have helped

wk9128 I'm looking for a further refinement, if possible. As you can see in the file I posted, the table is derived from a comma-delimited list (CDL). Is there a similar method of extracting the list direct from the CDL, without the need of the table? This would be most useful, as my spreadsheet is very complex and these tables take up about 90% of the columns. Cheers, Paul

How about

Formula:

Please Login or Register  to view this content.

although there are values in A2A6 that do not exist in your table

@paulma1960 late reply , @Fluff13 did a great job . Thanks

How about

Formula:

Please Login or Register  to view this content.

I take it I would need to know XML to use it effectively

Not really, I know nothing about XML codeing

Originally Posted by Fluff13

Not really, I know nothing about XML codeing

What's a good resource for learning more? These formulas are very, very useful.

Your formula above is also excellent, for me, in terms of selecting a colour. How about a date test? Each item has a date that needs to be checked against another date (and each item's date is unfixed, ie, not tied to other items' dates)?

What's a good resource for learning more?

Ufortunately I don't know of any resources relating to the Filterxml function.

I'm afraid I have no idea what you mean by

Each item has a date that needs to be checked against another date (and each item's date is unfixed, ie, not tied to other items' dates)?

In the file I uploaded, col U, there's an expected date, and the actual date that I need to compare with. Imagine, if you will, in col V with other dates, and each date in col U needs to be compared with the date in col V.

There are no dates in col V

I know that, that's why I suggested, 'imagine, if you will' that there are...

Ok, you can put a date in V11 & use

Formula:

Please Login or Register  to view this content.

The formula in W11 works for me, I see
Excel 2016 (Windows) 32 bit

	W
10	Conditional
11	AB_01
12	AB_21
13	AB_8.20
14	AB_8.21
15	AB_8.28

Sheet: Sheet1

And if I change the formula to look at W8 instead of hard coding the colour it still works

I'm not following your cell references. The filter-by-colour formula is in cell Y11. It works for me, both with drop-down selection and hard-coded selection for all the colours except when I select Blue or Green. This seems very weird to me.

The formula in cell Z11, for filtering by late date isn't working at all...

With the workbook from post#19, there are no formulae in Y11 or Z11, just W11.

The reason you get a #calc error for blue & green, is that there are no matches for those colours.

OK, that was weird. Somehow I posted the wrong workbook, so much confusion all round, it seems. I've renamed it (#3). The formulas I'm having problems with are in cells Y11 and Z11.

Ok, you cannot do that with countifs, try it like

Formula:

Please Login or Register  to view this content.

although in that data there are no cells where U is less then Y

That's excellent, thanks Fluff13. It'll take me a while to understand what's going on, but I had no problems implementing your solution. I'm attaching the sample file, formula is in cell V11, for anyone who might want to take advantage. Cheers...

Glad to help & thanks for the feedback.

Fluff13, I can't get my data to work for me, with what you've suggested. My data is very complex, and I think I over-simplified it in the workbook I uploaded. In the workbook I'm uploading now, I'm looking for milestones that are running late. The milestones are in H4:J8, and their dates (if available) are in cells K4:M8. I'm not interested in milestones that don't have a corresponding date, and I'm looking for the list of milestones whose expected date is later than the planned date (which are in red).

In cell H12, I've extracted the milestones in the milestone bucket, and in cell K12 I've extracted the dates from the date bucket. So, I want to interrogate the date bucket and capture only those dates running late, with the milestones that are late. The desired list you can see at cell N12. The table in columns B:C can be ignored, they're relevant only in that the milestones in col E need their dates.

How about

Formula:

Please Login or Register  to view this content.

Maybe

Formula:

Please Login or Register  to view this content.

Glad it's sorted & thanks for the feedback.

Thanks, Bo_Ry, that seems to be a suitable alternative solution to my original problem, though I've moved on to other issues at present. I'll revisit it with your suggestion when I can. Cheers...