Disappearing Data when duplicating text

MCB123x

New Member
Joined
Jun 29, 2020
Messages
30
Office Version
  1. 2007
Platform
  1. Windows
Hello,

I'm using this formula =IFERROR(INDEX(JN,MATCH(0,COUNTIF(JN,"<"&JN)-SUM(COUNTIF(JN,C$7:C9)),0)),"") which pulls a list from a column and puts it in another column, however as soon as I enter a duplicate name/word, it disappears from the list and leaves a blank cell. If this is repeated other names drop off from the pulled list. IDK what is happening.

Any help will do. Thank you.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,345
The COUNTIF counts the duplicate items more than once, which throws off the count. So if you have a duplicate name/word, do you want it to show up one time, or for every instance? And you want the list sorted?
 

MCB123x

New Member
Joined
Jun 29, 2020
Messages
30
Office Version
  1. 2007
Platform
  1. Windows
I got the formula off of mr.excel spreadsheets and just changed the cell the data was in. but for some reason this is happening on mine. I have no idea about those kind of formulas, but I copied it as it was doing what I was looking for, but then this happened.
 

MCB123x

New Member
Joined
Jun 29, 2020
Messages
30
Office Version
  1. 2007
Platform
  1. Windows
This is his video (
) I found it on (it's over 10yrs old) but as I'm using office 2007, it fits
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,345

ADVERTISEMENT

OK, fair enough. You found a formula that worked for you before, but with changing data, it no longer works. I have not seen that video, nor do I plan to watch it. I generally try to create a new formula based on your current needs. And to do that, I need the answers I asked in post 2. You want a sorted list, with spaces removed(?), and with duplicates removed(?).

For what it's worth, I'll probably try to adapt the formula from this link to your situation. You can certainly look at it yourself if you wish and try to figure it out.

 

MCB123x

New Member
Joined
Jun 29, 2020
Messages
30
Office Version
  1. 2007
Platform
  1. Windows
Ok, this is what I'm looking to do. I need to filter a list(customers) that grows every other day depending on the day (the day isn't important, although it does leave a gap in the column) so that there are no duplicate names. Then I need to pull from another list (Amt sold) to sum the amt the customers brought. If this sum equals or more than 12 I need it to display in that filtered list.
 

MCB123x

New Member
Joined
Jun 29, 2020
Messages
30
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Well, many thanks. I used that link above and of course changed the addresses of the columns and it worked a charm. The names aren't necessary need to be sorted in ABC order, so it's great.

Thank you.
 

MCB123x

New Member
Joined
Jun 29, 2020
Messages
30
Office Version
  1. 2007
Platform
  1. Windows
:). And if I may, if possible. I'd like that name from that list to only show up when the pcs column adds to =>12. This is the formula: =SUMIF(C$8:C$16,S$10:S$16,E$8:E$16), this pulls the count to the person in the list and I have a condition where if =>12 it highlight green.


The pulled list formula is:=IFERROR(INDEX($C$8:$C$28,MATCH(0,COUNTIF($S$9:S9,$C$8:$C$28),0)),"")

Can I add to that to do the above?
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,345
Try:

Book1
CDEFS
8al13
9bea2Name
10cal16al
11dyan3bea
12cal2cal
13bea12ed
14ed7aaron
15ed7 
16 
17aaron8 
18mark8 
19 
20aaron7 
21 
22 
23 
Sheet11
Cell Formulas
RangeFormula
S10:S23S10=IFERROR(INDEX($C$8:$C$28,MATCH(0,COUNTIF($S$9:S9,$C$8:$C$28)+(IF(SUMIF($C$8:$C$28,$C$8:$C$28,$E$8:$E$28)<12,0.5)),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,421
Messages
5,547,823
Members
410,813
Latest member
Vhinzvirgo
Top