I need some macros, basic Excel programming. Real deal, I need it to be done. Please write in your bid "excel2010".
There are 5 sheets in attached xls file.
1. First one (1)"XML" has list of products with details.
In columns 'Q' & 'T' are different images of the same product, rows are duplicated for each image.
Products are best distinguished by code in column 'L'.
2. Second sheet (2)"EXPORT ISTORE" has data exported from an e-shop.
3. In third sheet (3)"IMPORT" I need to have data copied from (2)"EXPORT ISTORE" but updated with fresh data from (1)"XML".
/offer/cena_brutto_detal -> 'Cena brutto'
/offer/cena_brutto_hurt_braz -> 'Cena promocyjna'
/offer/category -> 'Kategoria sklepowa' (fields containing 'Torby' should be changed to 'Torby Męskie')
/offer/brand -> 'Producent'
/offer/code -> 'Symbol'
/offer/color -> 'Kolor'
/offer/fabric -> 'Materiał'
/offer/quantity ->'Ilość w magazynie'
/offer/deliver_time_houres -> 'Wysyłamy w ciągu' ("1w" if quantity/Ilość w magazynie is 0, "24h" if quantity is more than 0)
/offer/description -> 'Opis HTML'
/offer/meta_keywords -> 'Słowa kluczowe'
/offer/meta_title -> 'Opis skrócony'
/offer/name -> 'Nazwa produktu'
/offer/warranty_months -> 'Gwarancja'
/offer/weight -> 'Waga'
Some of the products in (1)"XML" can no longer be found in (2)"EXPORT ISTORE". If there is a product with a code/Symbol in (1)"XML" that does not exist in (2)"EXPORT ISTORE", it needs to be added at the bottom of the (3)"IMPORT" sheet and highlighted in green.
This needs to be checked also the other way. If there is a product (code/Symbol) in (2)"EXPORT ISTORE" that no longer exists in (1)XML, it should be still copied to (3)"IMPORT" but the row highlighted in red.
For the rest of the fields:
'Identyfikator produktu' - empty for a new product row copied from (1)XML, the same for ones that already exist
'Status promocja' - new item gets "N", old stays as it was
'Status nowość' - new item gets "Y", old stays as it was
'Aktywny' - new item gets "Y", old stays as it was
'Dostępność produktu' - new item gets "D", item that is no longer available (highlighted in red) should be changed here to "NZ"
'Kategoria iStore' - the same for old and new items
'Gabaryt' - the same for old and new items
Additionaly there need to be run a check for duplicate names in "Nazwa produktu". Such issue could be marked with another third color.
4. Sheet (4)"EU STORAGE" is simple. It should have another button with a macro that copies things from (3)"IMPORT", if they are different.
'Nazwa produktu' -> 'Nazwa'
'Waga' -> 'Waga'
'Ilość w magazynie' -> 'Stan'
'Cena promocyjna' -> 'Cena zakupu'
'Gabaryt' -> 'Gabaryt'
'Katalog' - stays the same for old and new items
'ID' - new item empty, old one stays the same
'Stawka VAT' - the same for all fields
Products and duplicates here should be distinguished by name (nazwa). Coloring same as before.
5. Sheet (5)"EU AUCTIONS". Also copied from (3)"IMPORT". Duplicates should can be distinguished by title (TYTUŁ) or code ([SYMBOL]).
'Symbol' -> '[SYMBOL]'
'Nazwa produktu' -> 'TYTUŁ' and '[NAZWA]'
'Cena promocyjna' -> '[BRUTTO]' and 'WŁASNE NOTATKI'
CENA_KT - should have a following formula: IF(D2<100; 9%*D2; 9+6%*(D2-100))+D2 (D2 are fields from column [BRUTTO])
'Opis HTML' -> '[OPIS]'
Now time for columns 'ZDJĘCIA' and [ZDJECIE_1], [ZDJECIE_2] etc. These are photos which should be taken from (1)"XML".
First photo of particular product (code/symbol) from column '/offer/image' should be put in 'ZDJECIA', the rest should go into '[ZDJECIE_X]' columns. First photos from '/offer/image', then from '/offer/gallery_image'. Sometimes there may be missing photos in one column so the first photo should be taken from the other.
If you have any questions just ask, I need it in 48 hours. I don't accept pasted bids without the keyword.
15 freelancers are bidding on average $36 for this job
Hello there! I think I’m the right person for this job because, I work with excel/VBA for several years, I’m responsible and I promise you won’t regret hiring me! Regards, Filipe Ramos
i am hard working i am doing this ................................................................................................................................
hi, I am new here, so please give a new freelancer the possibility to convince you. I promis, I will not disapoint you! I have more then 3 years experience with VBA Thanks for your feedback Regards Bjorn
I am 7 years of experienced in VB.Net and VB6 with SQL Server and have worked on many such VB6 projects for development and modifications which includes formulas, formatting, complex calculations & graphs.