Eliminate duplicate text

[QSGuy]

Hey All

I have a spreadsheet where I need to eliminate duplicate text as attached - basically it is the Spec Tag column I want to eliminate duplicates from

It strikes me it should be pretty simple, but for some reason I can't get any of my usual formulas to work

Anyone any ideas?

[Bo_Ry]

Please try at F4

=IFERROR(INDEX(C$1:C$61,AGGREGATE(15,6,ROW($C$4:$C$61)/(MATCH($C$4:$C$61,$C$1:$C$61,)=ROW($C$4:$C$61)),ROWS(F$4:F4))),"")

[Glenn Kennedy]

in F4:
=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW($C$4:$C$61)/(COUNTIF(F$3:F3,$C$4:$C$61)=0),1)),"")


and in G4:

=IF(F4="","",VLOOKUP(F4,C:D,2,FALSE))

[CARACALLA]

F4=IFERROR(INDEX(C$4:C$100,SMALL(IF(FREQUENCY(IF(C$4:C$100<>"",MATCH($C$4:$C$100&$D$4:$D$100,$C$4:$C$100&$D$4:$D$100,0)),ROW(C$4:C$100)-ROW($C$4)+1),ROW(C$4:C$100)-ROW($C$4)+1),ROWS($C$4:C4))),"")

Control+shift+enter

copy across and down

[CK76]

Your result seems bit off. CLG-22 is also present in Spec Tag column, but not in your result required.

As well, there are instances of CLG-30 A & B, which has different Specification Description (both A & B has 2 description, which are duplicated).

If you only need unique from Spec Tag... assuming Excel 2016.
In F4:
=INDEX($C$4:$C$61,MATCH(0,COUNTIF($F$3:F3,$C$4:$C$61),0))

Confirmed as array (CTRL + SHIFT + ENTER). Copy down.

[ChemistB]

In F4 copied down

Please Login or Register  to view this content.

In G4 copied down

Please Login or Register  to view this content.

Does that work for you?

Note: You missed CLG-22 in your unique listing. I didn't notice the differing descriptions. I assume those are errors. Also, this formula is not an Array formula (Don't need CNTRL SHFT ENTER).

[Pete_UK]

I may as well give you another approach. Put this formula in A4:

=IF(D4="","",IF(COUNTIF(C$4:C4,C4)=1,MAX(A$3:A3)+1,"-"))

Copy down to the bottom of your data, to identify the first occurrence of a spec-code. Then you can use this in E4:

=IFERROR(INDEX(C:C,MATCH(ROWS($1:1),$A:$A,0)),"")

Copy into F4, then copy E4:F4 down until you start to get blanks.

Same comments apply as in Post #5.

Hope this helps.

Pete

[QSGuy]

Thanks Bo Ry - That works!

[QSGuy]

Glen

Yep, that works! Thanks!

I had been trying to work it out myself for a couple of hours before I posted and one of the options I was trying to work with was countif - but I had it in the wrong place!

[QSGuy]

Thanks for your response

For some reason, I could not get your formula to work - it brought up a blank cell

[QSGuy]

ChemistB

Thanks for your response! Yep, that works. Interesting to see another way of doing things...

I realised I had missed CLG-22 form the answers only after I copied the first response into the spreadsheet: busy day!

[QSGuy]

CK76

The A and B relates to the fact that there are two versions of that spec with very slight differences.

I could not get your formula to work - the first two lines worked, then it repeated CLG-155 for the rest of the spaces

[QSGuy]

Everyone

Thanks for your responses!

I wondered if there was a way to put the list in the correct numerical order? My immediate priority was to remove the duplicates, that is a great help! But it strikes me it would be handy to be able to put the list in numerical order as the actual specfications can then be read more straight from the list

[ChemistB]

I checked everyone's formula's and they should work. I took the liberty of putting them all into this workbook (Columns F and G). Only CARACALLA's formula looks at both columns to pull unique values. My guess is that you didn't perform CNTRL SHFT ENTER for the arrayed formulas.

TO your new question: Have you considered a Pivot Table? I put a pivot table in Column I, Format as Tabular with repeating labels. I pulled over (into "Rows") both columns C and D so it works more like CARACALLA's formula. If you aren't worried about that, then just pull over Col C and use a VLOOKUP. See Attached.

[QSGuy]

ChemistB

I tend to try and not use pivot tables as I use the information that such formulas spit out as the basis of other formulas and I find it difficult to understand where the info has come from in the table. In this case I will be using the Tags list as the starting point to bring in information from the actual specs in another workbook and also in another workbook for a pricing schedule. It will just be simpler to use formula rather than a pivot table.

[Glenn Kennedy]

You can, but it requires an array formula:

=IFERROR(INDEX($C$4:$C$61, MATCH(SMALL(IF((COUNTIF($F$3:F3,$C$4:$C$61)=0), COUNTIF($C$4:$C$61, "<"&$C$4:$C$61), ""), 1), COUNTIF($C$4:$C$61, "<"&$C$4:$C$61), 0)),"")

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[CK76]

In this case I will be using the Tags list as the starting point to bring in information from the actual specs in another workbook and also in another workbook for a pricing schedule.

In such cases, I'd strongly recommend using Power Query to transform data from both workbooks and load them to data model.
You can then create relationship within data model and create pivot table to report on it. Without need to worry about maintaining various formulas in worksheet.

Though it may seem a lot of work at beginning, I usually find that downstream maintenance is much easier and more than makes up for the initial workload.

Sample M code to generate result (assuming you want distinct record based on both A & B columns).

Please Login or Register  to view this content.

Steps:
1. Create Dynamic Named Range "myData" with following formula.

Please Login or Register  to view this content.

2. Data -> Get & Transform -> Get Data -> From Other Sources -> Blank Query (or you can skip rest of the step by copying and pasting M code above in Advanced Editor in query editor).

3. Enter following as Source.

Please Login or Register  to view this content.

4. Promote First Row as header.

5. Select any column and right-click remove Empty.

6. Remove [Room] column.

7. Select both remaining columns and right click, remove duplicates.

8. Return result to worksheet.

You can right click and refresh table to bring in new data if it's added to original data.

See Query1 sheet in sample attached. To see applied steps in query, go to Data -> Queries & Connections. And double click on Query1 in Queries pane.

[QSGuy]

Glen

The formula worked fine in the ceilings section, but when I tried to put it into the walls and floors sections, which are longer, it did not work - see sheet 2 in teh attached

At first it did bring in the correct order - or something like it - in the floors section, see column G in the attached that shows what came up

column F is the answer column in this case blank columns are to replicate the actual spreadsheet

[QSGuy]

CK

I will come back to reply to you later as I will need to get my head around your response!

[Glenn Kennedy]

OK. In your sample sheet (which was OK) the formula was set to the exact range of your data. In sheet 2 col F it was set too long. It fell over.

Option 1 (now col G). Set exact range. It works perfectly, but is a pain 'cos you need to maintain the formula when you add/subtract things.

Option 2 (now col H). Use a Dynamic Named Range. This auto adjusts the range for you as you add/subtract values in the list. It still doesn't like gaps in the middle of the data, but adjusts to ensure that none exist at the end of the range. Its other advantage is that it makes the formula more human readable.

I created a Named Range called .... wait for it.... Floors. CTRL F3 to view/edit. It looks like this:

=Sheet2!$N$5:INDEX(Sheet2!$N:$N,MATCH("Zzzz",Sheet2!$N:$N))

The array formula for the sorted list now becomes:

=IFERROR(INDEX(Floors,MATCH(SMALL(IF((COUNTIF($H$4:H4,Floors)=0), COUNTIF(Floors, "<"&Floors), ""), 1), COUNTIF(Floors, "<"&Floors), 0)),"")

see fle.

[QSGuy]

Glenn

I thought I had replied to this:

Yep, I see now that I need to have the exact range!

I chose the first option as, if I do need to add things in, it is easy to change the formula once and copy down.

Thanks for your help!

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

[QSGuy]

Hey Glen

It tells me I must spread some reputation around before giving it to you again!

I thought I had pressed it for everyone!

I have marked the thread Solved

[QSGuy]

Cancelled - the formula does work in 365

Glen

Should the last version of the formula (see below ie the one that sorts the list alphabetically) work in Excel 365?

IFERROR(INDEX($C$4:$C$61, MATCH(SMALL(IF((COUNTIF($F$3:F4,$C$4:$C$61)=0), COUNTIF($C$4:$C$61, "<"&$C$4:$C$61), ""), 1), COUNTIF($C$4:$C$61, "<"&$C$4:$C$61), 0)),"")

I can't seem to get it to work: it just keeps repeating the first result when I drag it down...

[JEC.]

For this formula, it depends on where you paste it. Like you show above, paste it at F5 and drag down. It should probably work that way.
I didn't test or openend any file, just a guess

[JEC.]

You can paste this everywhere

Please Login or Register  to view this content.