|
(Mtanislav) Duplicates
|
|
| Useful | Date: Saturday, 14.05.2011, 2:58 PM | Message # 1 |
 Expert
Group: Administrators
Messages: 183
Status: Offline
| Mtanislav your answer posted here
is this result that you've asked?
(click the file below that defined as your name)
 Hədəfə doğru ən qısa yol hər zaman mövcuddur!
|
| |
| |
| Useful | Date: Saturday, 14.05.2011, 5:48 PM | Message # 2 |
|
Expert
Group: Administrators
Messages: 183
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))
Hədəfə doğru ən qısa yol hər zaman mövcuddur!
|
| |
| |
| Qonaq | Date: 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
|
| |
| |
| Useful | Date: Saturday, 14.05.2011, 6:02 PM | Message # 4 |
|
Expert
Group: Administrators
Messages: 183
Status: Offline
| ok i'll try
Hədəfə doğru ən qısa yol hər zaman mövcuddur!
|
| |
| |
| mtanislav | Date: 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.
|
| |
| |
| Useful | Date: Saturday, 14.05.2011, 6:45 PM | Message # 6 |
|
Expert
Group: Administrators
Messages: 183
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
Hədəfə doğru ən qısa yol hər zaman mövcuddur!
|
| |
| |
| mtanislav | Date: Saturday, 14.05.2011, 6:49 PM | Message # 7 |
|
Group: Guest
| thank you very much. u'r n. 1
|
| |
| |
| Useful | Date: Saturday, 14.05.2011, 6:50 PM | Message # 8 |
|
Expert
Group: Administrators
Messages: 183
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!
|
| |
| |
| mtanislav | Date: 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
|
| |
| |
| Useful | Date: Saturday, 14.05.2011, 10:29 PM | Message # 10 |
|
Expert
Group: Administrators
Messages: 183
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!
|
| |
| |
| Useful | Date: Sunday, 15.05.2011, 5:05 PM | Message # 11 |
|
Expert
Group: Administrators
Messages: 183
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
Hədəfə doğru ən qısa yol hər zaman mövcuddur!
|
| |
| |
| mtanislav | Date: 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 :)
|
| |
| |
| mtanislav | Date: Monday, 16.05.2011, 0:11 AM | Message # 13 |
|
Group: Guest
| I get an error: Source reference overlaps destination area
|
| |
| |
| Useful | Date: Wednesday, 09.05.2012, 11:08 PM | Message # 14 |
|
Expert
Group: Administrators
Messages: 183
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!
|
| |
| |
