extract unique value but with condition

[apelsunkist]

hello,
before i start my question, i would like to said thank you to you guys in this forum. im working on something that was complicated for me, since i almost have no knowledge on most excel formula before, but by reading questions and answers in this forum i was able to built it bit by bit. and it almost done, just several last thing to done. this below is one of those last thing, where i get stuck on :P

i'm trying to extract unique value among duplicates. it is do really not that complicated, but when combined with condition and the need to automation, my eyes gone swirled a bit :P . could you guys inspire to how to accomplish this?
any helps would be appreciated
here is the sample :

the source table would be :

	A	B	C	D	E
1	date	name	bill number	payment	amount
2	mar-1	hanson	5001	less	(2000)
3	mar-4	hanson	5022	less	(2000)
4	mar-6	tika	6225	less	(3500)
5	mar-7	hanson	5022	less	(2000)
6	mar-8	galuh	4008	more	3000
7	mar-8	tika	6225	less	(3500)
8	mar-8	lulu	5758	less	(500)

<tbody>
</tbody>

the result wanted is :
to extract unique value (of the customer name), and where the word "less" become the keyword for condition (and automation).

such this :

	H	I	L
1	date	name	amount
2	mar-7	hanson	(2000)
3	mar-8	tika	(3500)
4	mar-8	lulu	(500)

<tbody>
</tbody>

to be exact, the condition would be :
1. extract customer name, which pay less than they should be (indicated by "less" in column-D). the value in column-I (name) would appear by it self with aoutomation,

2. colect unique value only from the name column (column-B).

3. take the result that is the latest date. in hanson example, mar-7 is the latest date from hanson.

a same name could be repeated with different bill number, but they are still a same person. and the same name and bill number could also be repeated for they can split the payment in twice or more. so the bill number is not an issue, the name alone that would distinct.

the range in initial table could be very long, and the amount of customers that is paid less, could not be predicted (thats why i use automation, so that they pop-up by them selves if there is one who paid less, or would stay empty if there is no one meets that citeria).

so far im using :
in i2 : =countif(d2:d8;i1)
in i5 : = IF(ROWS(i$5:i5)<=i$2;INDEX($b$2:$b$8;SMALL(IF($d$2:$d$8=i$1;ROW($b$2:$b$8)-ROW($b$2)+1);ROWS(i$5:i5)));"")
enter with Ctrl+Shift+Enter than drag down to necessary range.
than using index in L5 : =IF(i5="";"";INDEX($e$2:$e$8;MATCH(i5;$b$2:$b$8;0);0)) and drag down. since the amount would be same for each person, even with different bill number, the index combined with match are sufficient for fulfill this.

i got the automation formula in col-i from mr. Aladin Akyurek and mr. PaddyD answer in other thread.
and the index and match is also from other thread.

the result is :

	H	I	L
1	criteria :	less
2	count if "less":	6
3
4	date	name	amount
5	mar-1	hanson	(2000)
6	mar-1	hanson	(2000)
7	mar-6	tika	(3500)
8	mar-1	hanson	(2000)
9	mar-8	tika	(3500)
10	mar-8	lulu	(500)

<tbody>
</tbody>

the formula for automation is miraculous. and i have been using this for various other uses in other table.
but in this issue, i think the formula is need to be modified a bit, so that it could give only a unique result. (as shown above, the name hanson is repeated, where the desired result is only each unique name show up once)
i got an impression that if the column-I (name) has settled i could use
index + match for column-L, to get the amount
and use lookup for column-H to get the latest date payment from the person name (or i do wrong?).
but the problem is the column-I. could you guys suggest on something?

i'm sory if this is a double post where some one else is already asking about it some where. i have try to search a bit, with several keywords, and read a bit. but my eyes are already swirled @_@ :P

 

[Fazza]

Selemat apelsunkist

I suggest a non-formula approach. It uses a database type approach that is great for this kind of data query.

I'll explain a pivot table, just because it is simpler. Though a query table might be better for you.

I hope you are familiar with pivot tables. If not, I can post a link to some information.

My description is for Excel 2003. It will be slightly different in newer versions.

Create a normal (non-dynamic) defined name for the data. This is to cover the headers and data. Shortcut CTRL-F3, I've used name DataAnda. You don't need to do this but it is easier for me to explain. (You can use just the worksheet name instead, btw.) Save the file.

From a new file, ALT-D-P to start the pivot table wizard. Choose external data source, next, Get Data, Excel files, OK, browse for your file, OK, see the 'Add Tables' dialog box and choose DataAnda, Add, then close the dialog box. Now you are in MS Query. Hit the SQL icon and replace all the text that is there with this from below, copy (CTRL-C) from below and paste (CTRL-V) to replace whatever/anything is there.

SELECT MAX(D.date) AS [Date], D.name, D.amount
FROM DataAnda D
WHERE D.payment='less'
GROUP BY D.name, D.amount

If you get a message about not being able to graphically represent ... just OK to acknowledge it. Then the 'open door' icon to exit MS Query. Now finish and make the pivot table. If you want, move the new worksheet containing the data back into the original source data file.

OK?

 

[Marcelo Branco]

-EDIT-
hadn't seen Fazza reply

But if you want a formula solution...

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Maybe something like this

H I J

Criteria	less
Unique names	3
Dates	Name	Amount
mar/07	hanson	-2000
mar/08	tika	-3500
mar/08	lulu	-500

<tbody>
</tbody>

Array formula in I2 ( to count unique names that meet the criteria)
=SUM(IF(FREQUENCY(IF($D$2:$D$100=I1,MATCH($B$2:$B$100,$B$2:$B$100,0)),ROW($B$2:$B$100)-ROW($B$2)+1),1))
Ctrl+Shift+Enter

Array formula in I5 copied down
=IF($I$2>=ROWS($I$5:I5),INDEX($B:$B,SMALL(IF(FREQUENCY(IF($D$2:$D$100=$I$1,MATCH($B$2:$B$100,$B$2:$B$100,0)),ROW($B$2:$B$100)-ROW($B$2)+1)>0,ROW($B$2:$B$100)),ROWS($I$5:I5))),"")
Ctrl+Shift+Enter

Array formula in H5 copied down
=IF(I5<>"",MAX(IF($B$2:$B$100=I5,$A$2:$A$100)),"")
Ctrl+Shift+Enter

Regular formula in J5 copied down (assumes the amount is the same irrespective of the date)
=IF(I5<>"",INDEX($E:$E,MATCH(I5,B:B,0)),"")
confirmed with just Enter

Hope this is what you need

M.