MVP logo
    Sizin Brauzer: , Versiya
Şənbə, 21.10.2017, 7:09 PM
Salam Qonaq | RSS
Ana səhifə | (Mtanislav) Duplicates - Forum | Qeydiyyat | Giriş
[ Yeni mövzular · Üzvlər · Forumun qaydaları · Axtar · RSS ]
Page 1 of 11
Forum moderator: Useful 
Forum » Forumlar » Excel Forumu » (Mtanislav) Duplicates
(Mtanislav) Duplicates
UsefulTarix: Şənbə, 14.05.2011, 3:58 PM | Mesaj # 1
Expert
Qrup: Administrators
Mesajları: 149
Reputasiya: 0
Status: Offline
Mtanislav your answer posted here
is this result that you've asked?
(click the file below that defined as your name)
Əlavə edilmiş fayl: mtanislav.xls(14Kb)


Hədəfə doğru ən qısa yol hər zaman mövcuddur!
 
UsefulTarix: Şənbə, 14.05.2011, 6:48 PM | Mesaj # 2
Expert
Qrup: Administrators
Mesajları: 149
Reputasiya: 0
Status: Offline
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))
Əlavə edilmiş fayl: 1703265.xls(14Kb)


Hədəfə doğru ən qısa yol hər zaman mövcuddur!
 
QonaqTarix: Şənbə, 14.05.2011, 6:57 PM | Mesaj # 3
Qrup: Qonaq





very nice work. Thank you very much. Hope you can help me again. Please check the post again
 
UsefulTarix: Şənbə, 14.05.2011, 7:02 PM | Mesaj # 4
Expert
Qrup: Administrators
Mesajları: 149
Reputasiya: 0
Status: Offline
ok i'll try

Hədəfə doğru ən qısa yol hər zaman mövcuddur!
 
mtanislavTarix: Şənbə, 14.05.2011, 7:04 PM | Mesaj # 5
Qrup: Qonaq





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.

 
UsefulTarix: Şənbə, 14.05.2011, 7:45 PM | Mesaj # 6
Expert
Qrup: Administrators
Mesajları: 149
Reputasiya: 0
Status: Offline
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

Əlavə edilmiş fayl: Sample.rar(17Kb)


Hədəfə doğru ən qısa yol hər zaman mövcuddur!
 
mtanislavTarix: Şənbə, 14.05.2011, 7:49 PM | Mesaj # 7
Qrup: Qonaq





thank you very much. u'r n. 1
 
UsefulTarix: Şənbə, 14.05.2011, 7:50 PM | Mesaj # 8
Expert
Qrup: Administrators
Mesajları: 149
Reputasiya: 0
Status: Offline
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!
 
mtanislavTarix: Şənbə, 14.05.2011, 8:55 PM | Mesaj # 9
Qrup: Qonaq





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

Thanks for your work

 
UsefulTarix: Şənbə, 14.05.2011, 11:29 PM | Mesaj # 10
Expert
Qrup: Administrators
Mesajları: 149
Reputasiya: 0
Status: Offline
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!
 
UsefulTarix: Bazar, 15.05.2011, 6:05 PM | Mesaj # 11
Expert
Qrup: Administrators
Mesajları: 149
Reputasiya: 0
Status: Offline
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
Əlavə edilmiş fayl: 0728415.jpg(19Kb)


Hədəfə doğru ən qısa yol hər zaman mövcuddur!
 
mtanislavTarix: Bazar, 15.05.2011, 7:27 PM | Mesaj # 12
Qrup: Qonaq





thanks, I will try it later tonight. Thank you very much for all your effort in solving this problem and helping a noob :)
 
mtanislavTarix: Bazar ertəsi, 16.05.2011, 1:11 AM | Mesaj # 13
Qrup: Qonaq





I get an error: Source reference overlaps destination area
 
UsefulTarix: Cümə axşamı, 10.05.2012, 0:08 AM | Mesaj # 14
Expert
Qrup: Administrators
Mesajları: 149
Reputasiya: 0
Status: Offline
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 » Forumlar » Excel Forumu » (Mtanislav) Duplicates
Page 1 of 11
Search:

Saytın məlumatlarından istifadə edərkən mənbəyə istinad zəruridir!
MyExcelWorld © 2010 - 2017
Site managed by uCoz
ILK-10 Azeri Website Directory