Creating unique list of values from multiple columns not in sequence.

Hello,

I have an inventory/sales Excel spreadsheet for our retail store that I've been working on/improving upon for the last few months. I've included a version of the file below (unfortunately vastly cut down to meet size limitations of forum attachments).

Basically, I have monthly tabs where we enter the daily sales info and then we have a monthly reports tab that tabulates all of the data into monthly summary reports. Everything works beautifully, but there is one part of the spreadsheet I'd like to improve, but just can't figure out how to accomplish.

For all the retail category sections, the items we sell is standard and does not change. However, in the "Accounts" section of each daily report, the items change day to day and the associated "Dept." varies as well. I use the "Dept." column to distinguish "Retail" sales from various account sales which get flagged as "DORR (A)", "FERRY (A)", "BUNKER (A)", & "GALLEY (A)". As you will see on the "Monthly" tab, all those various items & associated accounts are compiled in the "Accounts" section of that page. Unfortunately, this is entered manually which invites user errors.

Here's a visual mock-up of the problem:
Lobstore Excel.jpg

I've researched the problem and found this formula (not adapted to my specifics) that will compile multiple columns into one unique list, but it assumes that all the columns are in sequence. It also does not meet my needs for also moving over the corresponding account names with each item.

Formula:

Please Login or Register  to view this content.

Is this possible to do via formulas, or does this need VBA?

Thanks for reading my post and any insight you might be able to share.

This proposed solution employs a helper table (Q107:V249) which could be moved and/or hidden for aesthetic purposes.
Column Q is a copy of the 'Item Description' column from the 'Ref Sheet'
Columns R:U are populated by the formula:

Formula:

Please Login or Register  to view this content.

Column V is populated by the array entered formula*:

Formula:

Please Login or Register  to view this content.

Column B, the Item Description column on the monthly sheet, is populated by the formula:

Formula:

Please Login or Register  to view this content.

Column E is populated by the formula:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

Hi JeteMC,

I just have one question regarding one of your formulas:

Formula:

Please Login or Register  to view this content.

What is the function of the entries 6553601 & "R0C00" in the formula?

The reason I ask this is because I am now doing the same thing for the "Other" category in the workbook and for some reason this formula is giving me the wrong values in the sorted unique list. Instead of "Oysters/CHRIS (O)", the formula is returning "FISH" on every line.

Excel Problem.jpg

Here is your formula adapted by me to suit the "Other" category:

Formula:

Please Login or Register  to view this content.

I can't see anything that should return such results, but I'm unfamiliar with the two entries I listed above.

If you would be so kind, could you take a quick peek.

Thanks.

***EDIT***
I figured-out the problem, but still clueless on why it is happening.

The problem happens once I get to column DD. Everything works great prior to that column, but as soon as I reach DD, the formula stops working.

Funny thing though, the other formulas work just fine from column DD onward. It's limited to just this formula.

The 6553601 is the value_if_false argument of the IF function and replaces FALSES so that when the MIN function picks a number 6553601 will be larger than the largest possible sum of the row number multiplied by 100 plus the column number. The "R0C00" part is the format_text argument of the TEXT function. This is sending INDIRECT row and column column coordinates in the fashion of the R1C1 reference style, as opposed to the more common A1 reference style.
If you select the cell V108 in the file attached to post #3 and then use the Evaluate Formula feature (Formulas tab) you can see that the MIN function picks 11018 which the TEXT function interprets as R110C18 (row 110, column 18 or R110 in the A1 reference style).
Try running the Evaluate Formula on your cell DD5 and see if the TEXT function returns R5C104 which is CZ5 in the A1 reference style.
Let us know if you have any questions.

Let's try this array entered formula* instead:

Formula:

Please Login or Register  to view this content.

It takes a little longer to run and the results are in a different order, however I think that it will convert to your category 'Other' table more reliably.
Let us know if you have any questions.