UNIQUE IF

[Anon9149]

Hi all,

I have a list of categories in column A. I have a list of sub-categories in column B.

I want to extract a unique list of sub-category names, with one catch; duplications of sub-category names should be allowed IF repeated in a different parent category.

For example, in column A there may be two categories; 'Costs of Sale' and 'Overheads'.
In column B, the sub-category 'Salaries' may appear once in 'Costs of Sale' and once in 'Overheads', in which case the sub-category 'Salaries' would appear twice in the unique list. If however, the sub-category is repeated within the same category, the unique list would only show one value per category.

I was wondering if this is achievable by combining UNIQUE and IF functions, as I can achieve this with multiple helper columns and other formulae, but I want to keep the number of formulae to a minimum as the workbook is already pretty large.

Thanks in advance!

EDIT:
Sample sheet attached

 

[pecoflyer]

Hi
please post a sample sheet ( no pics) with some data and manually calculated results. thx

 

[Peter Bartholomew]

You should at least get a start on this problem by applying the UNIQUE function to a range that encompasses both columns.


That wil return the unique combinations of the two columns combined.
It gets a little harder if the two columns are not adjacent in the table, but still possible.

 

[Anon9149]

You should at least get a start on this problem by applying the UNIQUE function to a range that encompasses both columns.

Thanks Peter. In my initial post I tried to simplify the problem as much as possible and omitted some details. In fact, I have indeed already applied the unique function to a range that encompasses both problems; UNIQUE(TOCOL(range)

At the risk of confusing things, it may help if I elaborate and explain why I am doing this. Attached an updated document.

I am creating a financial reporting pack and to achieve this, I essentially need a list of all unique values from Category 1 (Parent group) to Category 5 (sub-sub-sub-sub-category) items in the 'List' column. I don't think it's necessary to explain the mechanics of the TOCOL function, so I'll skip right to the problem.

In Category 5 you'll see on rows 19 - 23 that we have five values:

• Legal & professional
• PPE
• Salaries
• Salaries
• Staff welfare

With respect to the 'List' (column G) the list goes from 'Legal & Professional' (row 25) to 'Staff Welfare' (row 26) and skips 'PPE' and 'Salaries' on the basis that these exact values already appear in the range on rows 11-13. I know why UNIQUE does this, I'm looking for a workaround that can generate a second 'PPE' and 'Salaries' value, on the basis that the prior values are in a separate 'Category 4' section.

This is still a very simplified version of the problem, but I think that's the extent of the detail needed, and I don't want to further overcomplicate unnecessarily.

 

[Peter Bartholomew]

If it is OK to divide the problem into two parts, the detailed category in column 5 and general categories in columns 1-4 then one could have

= LET(
    categories,  DROP(Table1, ,-1),
    distinctCat, UNIQUE(TOCOL(categories,,1)),
    detail,      TAKE(UNIQUE(Table1),, -1),
    VSTACK(distinctCat, detail)
  )

That wouldn't, however, capture the fact that cat4 'External' may be 'Operational' or 'Non-operations' from cat3.

 

[p45cal]

A poosibility, in the attached in column G five formulae which will have repeating subcategories.
In column I I've stacked the results
In column K, a single formula skipping the need for the intermediate formulae.
Is this the sort of thing you were looking for?

This is very similar to Peter's solution except (a) I've not included Category1 (because you said you want a list of sub-category names in your first post) but it could easily be added, and (b) there are 2 instances of External for Category4, 2 instances of Operational and 2 of Non-operational for Category3.

 

[Peter Bartholomew]

This treats the table as tree structure and only removes duplicates within the same branch.

= REDUCE("List",{1,2,3,4,5},
      LAMBDA(combined,k,
          LET(
              categories, TAKE(Table1, ,k),
              distinct,   TAKE(UNIQUE(categories), ,-1),
              VSTACK(combined, distinct)
          )
      )
  )

(it may be the same as Pascall's?)

 

[Peter Bartholomew]

This is the same formula with the exception that it uses HSTACK and ISNA rather than VSTACK

 

[Anon9149]

Good morning everyone,

First of all, allow me to thank you for taking the time to provide solutions to this. My apologies for not responding sooner. I've looked at each one and as far as I can see, all of them provide a solution, just not in the format I require. The reason for my wanting to create the 'List' is so that it can be used as the foundation for the financial statements, which read linearly. The logic then, would be as follows:

1. Beginning from the top left cell in the 'Category' array, the formula scans scan horizontally from left to right i.e. from Category 1 to Category 5 i.e. from Parent Category to sub-category
2. Once the formula detects a unique value in a category that has not yet been generated in the List, it generates that unique value in the List on the next available row, then proceeds scanning the rest of the data in accordance with (1) above
3. If the formula detects a value in a category that has already been generated in the List, it ignores it and continues to scan for the next unique value (*), first by continuing to scan rightwards and then proceeding to move down to the adjacent row and repeating the process.

* With the exception of category 5, where a duplicated value can be repeated in the List, provided the same values generated earlier in the List belong to a different Category 4

To explain in a different way why the solutions above aren't exactly what I need, again I reiterate that this list will be used as the foundation for the presentation of the financial statements. So imagine reading a set of financial statements where, instead of reading linearly i.e. having a 'Revenue' section at the top with a breakdown of all the revenue streams underneath, followed by an expenses section with a breakdown of all the individual line-item expenses underneath (i.e. the correct presentation), instead you have a set of financial statements that presents the titles of all the category 1 headers first, the category 2 headers second, the category 3 headers third, and so on, with all the line-items presented last and no segregation for class (expenses and revenue line items presented in one block).

I know there are other solutions to this e.g. using a static template, but for a number of reasons I need this to be dynamic. It doesn't have to make use of UNIQUE and if necessary then we can make use of helper columns. I don't have any knowledge of VBA though, so I'd prefer to steer away from that.

Attached is an updated sample and I've updated the categories to better suit the needs of the end users. As for the blank values in Cat. 5, in the real document these are dynamic ranges that are updated if the end user (overseas finance manager) wishes to add additional revenue streams, additional costs of sales etc. to tailor the document to their individual company. The 'List' ignores these blanks.

You'll be able to see that the 'List' begins to somewhat resemble a linear P&L i.e. converts into a linear (categorised) format rather than a tree structure. Revenue headers presented in rows 3-5 followed by the individual revenue streams below in rows 6-12, followed by the Expenses header in row 14, followed by the individual expense items from row 16).
Problem: You'll also note that row 13 is the External Revenue header, but the list doesn't show the external revenue streams underneath due to the fact the values (E22 - E28) have already been generated in the list (refer to G6 - G12; see E2 - E8 for original values).

I know the List doesn't resemble a finalised P&L, but I have already been able to configure sub-total lines (total internal revenue, total external revenue etc.), total lines (total revenue, total expenses etc.) and grand-total lines (gross profit, EBITDA etc.) into the document, as well as configure appropriate spacing between categories and total lines, so literally everything is in place and this is the last piece of the puzzle.

 

[Anon9149]

I think I may have found a workaround, by concatenating values in categories 4 & 5 to create a [second] column for category 5, where all cat. 5 values are unique as they're branched to cat. 4 values. I'm going to have to see whether this impacts on the ability to produce conventional financial statements (which I already configured) but if it does, I should be able to figure a workaround.

Thanks again for all your efforts!