MVP logo
    Your browser: , Version
Thursday, 16.08.2018, 9:42 PM
Hello! Guest | Group Guest | RSS
Home | (Mtanislav) Duplicates - Forum | Profile  | Login
[ New messages · Members · Forum rules · Search · RSS ]
  • Page 1 of 1
  • 1
Forum moderator: Useful  
Forum » Forums » Excel Forum » (Mtanislav) Duplicates
(Mtanislav) Duplicates
UsefulDate: Saturday, 14.05.2011, 2:58 PM | Message # 1
Expert
Group: Administrators
Messages: 183
Reputation: 0
Status: Online
Mtanislav your answer posted here
is this result that you've asked?
(click the file below that defined as your name)
Attachments: mtanislav.xls(14.0 Kb)


Hədəfə doğru ən qısa yol hər zaman mövcuddur!
 
UsefulDate: Saturday, 14.05.2011, 5:48 PM | Message # 2
Expert
Group: Administrators
Messages: 183
Reputation: 0
Status: Online
Oh yes then try this formula
(array formula) enter with Ctrl+Shift+Enter

Code
=MIN(IF($B$10:$B$15=D10;$A$10:$A$15))
Attachments: 1703265.xls(14.0 Kb)


Hədəfə doğru ən qısa yol hər zaman mövcuddur!
 
QonaqDate: Saturday, 14.05.2011, 5:57 PM | Message # 3
Group: Guest





very nice work. Thank you very much. Hope you can help me again. Please check the post again
 
UsefulDate: Saturday, 14.05.2011, 6:02 PM | Message # 4
Expert
Group: Administrators
Messages: 183
Reputation: 0
Status: Online
ok i'll try

Hədəfə doğru ən qısa yol hər zaman mövcuddur!
 
mtanislavDate: Saturday, 14.05.2011, 6:04 PM | Message # 5
Group: Guest





how do I implement it for this sample

download the sample data from here http://www.2shared.com/document/2Vlm8cWU/sample.html (the sample only has a 20 items, the original one has 21000) and some products are the same from even 8 suppliers.

I want to get rid of the duplicate products with the same reference and keep just the one with the smallest price.

I hope I explained now correctly.

Thanks again for all your help.

 
UsefulDate: Saturday, 14.05.2011, 6:45 PM | Message # 6
Expert
Group: Administrators
Messages: 183
Reputation: 0
Status: Online
Quote (mtanislav)
want to get rid of the duplicate products with the same reference and keep just the one with the smallest price.

Dear Mtanislav simply paste your original data to from the range A10 and B10 and it'll work as ordinary (i've changed range in the formula it'll fit your range)
try this

Added (14.05.2011, 5:29 PM)
---------------------------------------------
mtanislav, here you are the result with the similar as your file that attached below
(have a look the answer sheet)

Added (14.05.2011, 5:45 PM)
---------------------------------------------
Now i've to go see you on monday i hope it'll help you

Attachments: Sample.rar(17.3 Kb)


Hədəfə doğru ən qısa yol hər zaman mövcuddur!
 
mtanislavDate: Saturday, 14.05.2011, 6:49 PM | Message # 7
Group: Guest





thank you very much. u'r n. 1
 
UsefulDate: Saturday, 14.05.2011, 6:50 PM | Message # 8
Expert
Group: Administrators
Messages: 183
Reputation: 0
Status: Online
Quote (mtanislav)
thank you very much. u'r n. 1

Not at all i hope see you soon!


Hədəfə doğru ən qısa yol hər zaman mövcuddur!
 
mtanislavDate: Saturday, 14.05.2011, 7:55 PM | Message # 9
Group: Guest





another solution to this is at http://www.mrexcel.com/forum/showthread.php?p=2716524

Thanks for your work

 
UsefulDate: Saturday, 14.05.2011, 10:29 PM | Message # 10
Expert
Group: Administrators
Messages: 183
Reputation: 0
Status: Online
Yeah array formula isn't working fast than the normal formula. If you have a table with great range then it'll took a lot time for calculation. Now i have a look there where you've linked and i thing you have an another choise. I suggest you to use pivot table to get perfect result good luck!

Hədəfə doğru ən qısa yol hər zaman mövcuddur!
 
UsefulDate: Sunday, 15.05.2011, 5:05 PM | Message # 11
Expert
Group: Administrators
Messages: 183
Reputation: 0
Status: Online
Hi dear Mtanislav really i want to help you. Yes pivot table gives the best report form but there is several steps (but not more). if you want i promise you i'll give you info about how creating pivot table. but now you have an onother way i suggest you to use "Consolidate" from Data/Data tools/Consolidate that is faster and shorter than the other ways. There is no more steps, i hope you'll enjoy from this result. Good luck!!!
(P.S:please find the info at "JPG" file that attached below)

for your result do this three steps
from function- choose "Min"
from reference- choose your range (that contain data)
from use labels in- click left column
Attachments: 0728415.jpg(19.0 Kb)


Hədəfə doğru ən qısa yol hər zaman mövcuddur!
 
mtanislavDate: Sunday, 15.05.2011, 6:27 PM | Message # 12
Group: Guest





thanks, I will try it later tonight. Thank you very much for all your effort in solving this problem and helping a noob :)
 
mtanislavDate: Monday, 16.05.2011, 0:11 AM | Message # 13
Group: Guest





I get an error: Source reference overlaps destination area
 
UsefulDate: Wednesday, 09.05.2012, 11:08 PM | Message # 14
Expert
Group: Administrators
Messages: 183
Reputation: 0
Status: Online
Quote (mtanislav)
I get an error: Source reference overlaps destination area

Good morning Mtanislav what is an error? can you discribe that?

Added (16.05.2011, 8:45 Am)
---------------------------------------------
I've cheked everything is ok. It'll take the range (without any errors) that you want to reference and in few seconds your result would be done. (If you attached your original file probably i could be check it easily)


Hədəfə doğru ən qısa yol hər zaman mövcuddur!
 
Forum » Forums » Excel Forum » (Mtanislav) Duplicates
  • Page 1 of 1
  • 1
Search:


MyExcelWorld © 2018
Site managed by uCoz