Disappearing Data when duplicating text

[MCB123x]

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.

 

[Eric W]

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]

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]

This is his video (

) I found it on (it's over 10yrs old) but as I'm using office 2007, it fits

 

[Eric W]

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.

https://www.myonlinetraininghub.com/excel-extract-a-unique-list

 

[MCB123x]

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]

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.

 

[Eric W]

Glad you got it sorted.

 

[MCB123x]

. 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?

 

[Eric W]

Try:

Book1
	C	D	E	F													S
8	al		13
9	bea		2														Name
10	cal		16														al
11	dyan		3														bea
12	cal		2														cal
13	bea		12														ed
14	ed		7														aaron
15	ed		7
16
17	aaron		8
18	mark		8
19
20	aaron		7
21
22
23
Sheet11

Cell Formulas
Range	Formula
S10:S23	S10	=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.