Elektroda.pl
Elektroda.pl
X
Proszę, dodaj wyjątek www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

excel/vba - porównanie dwóch arkuszy

maryluu2014 02 Gru 2014 22:22 2538 29
  • #1 02 Gru 2014 22:22
    maryluu2014
    Poziom 6  

    Witam. Bardzo proszę o pomoc w powyższym temacie. Mam dwa arkusze, każdy po kilka tys wierszy, które muszę porównac w ten sposób:
    1. wyszukać pierwsze PO number z arkusza "dane w arkuszu "forecast"
    2. porównać kolejne kolumny : material no, quantity, date.
    3. jeśli wartości się różnią to wyszczególnij kolorem czerwonym określone, błędne komóRKi i w kolumnie 5 wpisz "popraw" w arkuszu "forecast"
    4 zaznaczyć każdy dodatkowy wiersz w arkuszu forecast, którego brak w ark dane.

    Przyznam się, ze próbowałam napisać kod, ale nie przyniosło to oczekiwanych rezultatów. nie potrafię napisać makro wyszukujące wartości z jednego ark w drugim , a póżniej porównujące kolejne kolumny.

    Niestety ten plik to moja zmora...jestem zmuszona robić wszystko ręcznie. Bardzo proszę o pomoc.

    0 29
  • #2 02 Gru 2014 22:39
    adamas_nt
    Moderator Programowanie

    Przeczytaj uważnie pkt.3. Przy tych danych w zasadzie wszystko można zaznaczyć na czerwono ;)

    Trzeba przyjąć jakieś założenie. Np jedna z dwóch wartości z kolumny C i D byłoby do zrobienia. No i będzie problem z pkt.4. Skąd makro ma "wiedzieć" czy to brak zgodnego wiersza, czy różnica (wcześniej pomalowana na czerwono)?

    0
  • #3 04 Gru 2014 19:57
    maryluu2014
    Poziom 6  

    Dziekuję bardzo za odpowiedź i W sumie to masz rację. Brakowało mi obiektywnego spojrzenia na ten cały bałagan. A gdyby to makro usuwało identyczne wiersze( wystepujące w obu arkuszach) z arkusza forecast, a zmiany ilości w danych datach zaznaczało na jakis kolor? a druga opcja- a gdyby wszystkie zmiany w odniesieniu do arkusza 2. kopiowało do trzeciego arkusza( tylko, ze wtedy musiałoby kopiować jedynie zmiany wystepujące w arkuszu forecast)?
    Chyba za długo juz próbuję coś zrobić z tym plikiem( bezskutecznie zresztą :( ) bo zupełnie brakuje mi świeżego spojrzenia.

    0
  • #4 04 Gru 2014 21:46
    adamas_nt
    Moderator Programowanie

    Po mojemu makro powinno działać w ten deseń:
    -przeniósłbym wszystkie "brakujące" wiersze do arkusza 'Dane', gdzie kryteria to kolumny A, B, D (Nr PO, materiał, data) - Może jednak odwrotnie? Tu nie mam pewności w którą stronę należy porównywać.
    -pozostałoby tylko wykazać różnice w "zgodnych" (kryteria jak wyżej), czyli ilość po prostu - dodatkowa kolumna z wartością?
    -wydzielić kolumnę na dane dla filtra, w których wklejamy informacje: "OK" lub "przeniesiony", lub "nieOK" (z liczbą w komórce obok)

    Mielibyśmy jakieś podstawy do analizy...

    0
  • #5 04 Gru 2014 23:19
    maryluu2014
    Poziom 6  

    Pomysł super, jednak arkusz dane to stan bieżący systemu, natomiast ark 2 to pożądany stan systemu. Porównanie ma na celu przyspieszyć wprowadzanie zmian do systemu. Celem jest poprawienie danych w systemie w ten sposób by były identyczne z ark 2. Istotne są zmiany
    y w trzech kolumnach : a, c, d( data niestety też jest bardzo istotna wartością). Gdyby makro zaznaczalo w ark dane np kolorem przeniesione, brakujące wiersze i faktycznie to po twojemu, to byłoby chyba to :). Zazdroszczę kreatywności :). Masz może jakiś pomysł, lub wskazówkę na makro. Do tej pory tworzylam bardzo proste kody np wczytujace kolumny z plików do danego arkusza, czy porownujace wiersze w dwóch arkuszach, ale położone równolegle. Zupełnie nie mogę sobie poradzić z makrem "szukajacym" wartości i dodatkowo je porownujace.
    Byłabym niezmiernie wdzięczna za jakiekolwiek wskazówki.
    Dziękuję bardzo za zainteresowanie tematem.

    0
  • #6 05 Gru 2014 08:53
    adamas_nt
    Moderator Programowanie

    Wklej do modułu i przeanalizuj

    Kod: vb
    Zaloguj się, aby zobaczyć kod

    Po zakończeniu 1-szej fazy, oznaczone mamy (tymczasowo, no końcu to usuniemy) sprawdzone już wiersze w arkuszu "dane". Pod warunkiem, że powyższe jest OK, można działać dalej.
    Jeśli tu masz jakieś uwagi, pomysły, sugestie - pisz śmiało.

    0
  • #7 05 Gru 2014 21:54
    maryluu2014
    Poziom 6  

    Jestem pełna podziwu - piękna robota.
    Nie pomyślałabym, ze można ten kod napisać w taki sposób,a uwierz mi próbowałam wielu. Pierwsza faza - super. Jedyne co mogłabym zmienić to to, aby najpierw porównywał kolumny ABC( bo daty zmieniają się najczęściej, a ilości znacznie rzadziej)a póżniej daty i aby w kolumnie M w ark dane pojawiały się zmienione daty.
    Z dumą muszę przyznać :), że potrafię to nawet zmienić sama :).
    Mam taki pomysł, nie wiem czy do końca realny -
    .
    -jeżeli a arkuszu dane jest więcej wierszy( z danego zamówienia) niż w ark 2 to zaznacz dodatkowe wiersze jako - "do usunięcia"
    - jeżeli w ark 2 jest więcej wierszy( z danego zamówienia) niż w ark1 to dodatkowe wiersze zaznacz jako " do wprowadzenia"
    -reszta " do poprawy"

    Moja znajomość vba jest podstawowa, wiesz zresztą,nie wiem czy utworzenie takiego kodu jest możliwe.
    Cudownie byłoby, gdyby makro zaznaczając, że dany wiersz jest błędny wypisywał nie tylko rożniące się daty, ale też ilości.
    Mimo wszystko jestem niezmiernie wdzięczna za pomoc i Twój cenny czas, który poświęcasz. Bardzo dziękuję

    0
  • #8 06 Gru 2014 08:42
    adamas_nt
    Moderator Programowanie

    maryluu2014 napisał:
    Z dumą muszę przyznać :), że potrafię to nawet zmienić sama :).
    No to gratuluję! Miło konwersować z osobą potrafiącą używać szarych komórek, co niestety nie jest w modzie ostatnimi czasy ;) Opanowanie VBA w takim razie to tylko kwestia czasu w Twoim przypadku :)

    Wracając do tematu. Wszystko jest do zrobienia. Po południu będę miał trochę czasu, spróbuję to rozwinąć. Ale zastrzegam: to tylko mój punkt widzenia sprawy.
    Może w międzyczasie ktoś z innych forumowiczów (mamy tu kilku/nastu Asów) dołoży swoje trzy grosze. W końcu: co sześć głów, to nie jedna...

    0
  • #9 06 Gru 2014 20:32
    maryluu2014
    Poziom 6  

    Super:). Czekam z niecierpliwością na Twój pomysł.

    0
  • #10 07 Gru 2014 09:35
    adamas_nt
    Moderator Programowanie

    Mam pewne wątpliwości. Sorki, ale nie miałem czasu wczoraj.
    Jak zinterpretować dane z wierszy 6 i 7 arkusza "dane:. W kolumnach ABC są identyczne. Który jest "do poprawy", który "do usunięcia"? Odpowiadający mu wiersz w "forecast" to 7, ale z różną datą...

    Jakie dane wynikowe powinno zwrócić makro? Co jest istotne (data, ilość, suma)?

    Może wpisz wyniki ręcznie i załącz ponownie, będzie łatwiej rozgryźć...

    0
  • #11 07 Gru 2014 11:10
    maryluu2014
    Poziom 6  

    Uzupełniłam plik ręcznie.Wyznacznikiem w każdym wierszu jest kolumna A z PO no, jeśli ilość wiersz dotyczących tego samego zamówienia jest różna w obu arkuszach wówczas:
    - jeśli w ark 1 jest wiecej wierszy tego samego PO niż w ark 2 to wiersze, które sobie "nie odpowiadają", te dodatkowe będą" do usunięcia"
    -jeśli w ark 2 jest więcej wierszy niż w ark 1 to te dodatkowe wiersze będą " do wprowadzenia"
    - pozostałe z różnymi wartościami w kolumnach C i D będą Qbad lub "do poprawy"
    - i zgodne wiersze- " ok"
    Tak naprawdę w informacja czy wiersz jest " do usunięcia"(ark dane), czy "do wprowadzenia"(ark forecast) decydują nie wartości, ale dodatkowe wiersze tego samego PO no.

    Zdaję sobie sprawę,że jest to bardzo skomplikowane ( szczególnie dla mnie), ale gdyby marko wyrzucało mi te komunikaty w odpowiednich arkuszach to byłoby dla mnie ogromnym ułatwieniem i dużą oszczędnością czasu.

    Dodano po 1 [minuty]:

    Przepraszam, zapomniałam o pliku :)

    0
  • #12 08 Gru 2014 11:10
    adamas_nt
    Moderator Programowanie

    Pytanie Nr 324 i 325/2014:
    Czy "PO no" jest i będzie zawsze liczbą (nie będzie Np "123a", "123ms" itd/itp)?
    Czy "material no" może zaczynać się od liczby (Np "3ms524", "23ms", itd/itp)?

    Jeśli TAK i NIE, to znacznie przyspieszymy fazę drugą (która ciut kulawo, ale już działa).

    0
  • #13 08 Gru 2014 11:52
    maryluu2014
    Poziom 6  

    Tak, PO no zawsze będzie liczbą. Natomiast material no, niestety może zaczynać się od liczb, albo od liter. Jest ponad 700 różnych części, o różnych kombinacjach cyfr i liter, niestety.

    0
  • #14 08 Gru 2014 12:58
    adamas_nt
    Moderator Programowanie

    Zakładam, że w kolumnie A może być ta sama wartość, a różna w kolumnie B. Np
    123 ms524
    123 ms524
    123 ms599

    Wpadłem na pomysł, żeby składać obie wartości do stringa. Porównywanie będzie wtedy znacznie szybsze. W związku z tym pytanie Nr 326/2014:
    Potrzebny jakiś znak rozdzielający. Np "@" lub coś, co nie jest używane w "material no".
    W czym rzecz. Przy fazie pierwszej złoży się string w kolumnie tymczasowej:
    123 ms524 > 123@ms524
    123 1ms52 > 123@1ms52
    1231 ms52 > 1231@ms52
    Można będzie wtedy porównywać Np:
    cells(argumenty) & "jakiś_znak" & cells(argumenty) <> NaszTymczasowyString_w_innym_arkuszu
    zamiast mielić w dwóch zagnieżdżonych pętlach.

    Pytanie 327/2014: Czy arkusze są już wcześniej posortowane, czy makro ma to zrobić (posortowane być muszą)?

    P.S. Mam nadzieję, że czas Cię nie goni, bo u mnie "wolne chwile", które mogę poświęcić przy klawiaturze, trudno ostatnio przewidzieć ;)

    0
  • #15 08 Gru 2014 14:30
    maryluu2014
    Poziom 6  

    Założenie prawidłowe- w kolumnie A ta sama wartość, natomiast w kolumnie B może być różna.
    Znak @ jest ok. Pomysł faktycznie, rewelacyjny :).
    Makro wczytujące dane, formatujace i sortujace ułozyłam wcześniej :).
    Jestem ci bardzo wdzięczna za pomoc, sama nie poradzilabym sobie. Na wprowadzenie zmian do systemu mam ok 2 tyg. Gdyby narzędzie było gotowe w ciągu tygodnia na pewno zdarzylabym to zrobić.

    0
  • #16 08 Gru 2014 21:50
    adamas_nt
    Moderator Programowanie

    Hmm... "125 tam175" jest jeden wiersz "do wprowadzenia", zdaje się.
    5 po jednej i 4 po drugiej stronie. Zgadza się, czy u mnie coś makro szwankuje?

    0
  • #17 08 Gru 2014 22:14
    maryluu2014
    Poziom 6  

    Tak,zgadza się. To ja popełniłam błąd w drugim, załączonym pliku.
    Czyli makro działa poprawnie...już nie mogę się doczekać :)

    0
  • #18 08 Gru 2014 22:22
    adamas_nt
    Moderator Programowanie

    Sprawdź, coś tam pewnie będzie do "wygłaskania". W razie czego nos będę miał w monitorze jeszcze jakiś czas ;)

    Fazę trzecią robiłem kilka godzin po fazie drugiej, więc jest z "innej beczki"...

    0
  • #19 08 Gru 2014 22:38
    maryluu2014
    Poziom 6  

    Sprawdzę działanie kodu na realnych danych jutro rano w pracy i dam znać. Z góry bardzo Ci dziękuję.

    0
  • #20 09 Gru 2014 12:53
    maryluu2014
    Poziom 6  

    Sprawdziłam i pojawia się błąd w fazie 3
    w tym miejscu:
    If .Cells(wrs, 6) = strAray(ar) Then (...)
    Prześlę ci cały plik

    0
  • Pomocny post
    #21 09 Gru 2014 20:10
    adamas_nt
    Moderator Programowanie

    Och, brakuje wyzerowania zmiennej na początku pętli. Tutaj ten fragment:

    Kod: vb
    Zaloguj się, aby zobaczyć kod

    Nie porównywałem tych danych "ręcznie", czy raczej "ocznie" :) Ale wszystko wyszło "do wprowadzania". Dobrze?

    0
  • #22 10 Gru 2014 09:51
    maryluu2014
    Poziom 6  

    Rewelacyjnie!!! sprawdziłam i działa :).
    Dzisiaj wieczorem pozmieniam jeszcze makro by porównywało początkowo ilości, poźniej daty i pokazywało zmienione wartosci. I dam znać jak działa " w całości"
    Nie wiem jak mam ci dziękować. Bardzo, bardzo dziekuję:)

    0
  • #23 10 Gru 2014 10:25
    adamas_nt
    Moderator Programowanie

    maryluu2014 napisał:
    pozmieniam jeszcze makro by porównywało początkowo ilości, poźniej daty
    ??? Nie ma takiej potrzeby. Makro w fazie I sprawdza i oznacza wg wszystkich czterech wartości. Zerknij do miejsca, gdzie If'y zamieniłem na Selct Case.
    Takie znakowanie jest niezbędne w fazie III, gdzie wybierany jest wiersz najmniej zgodny.

    0
  • #24 10 Gru 2014 20:55
    maryluu2014
    Poziom 6  

    Sprawdziłam działanie pliku, rozpatrzyłam wszystkie przypadki. Niestety nie zawsze makro wpisuje "do usunięcia" i kiedy dane wiersze różnią się np. tylko datą wpisuje "Qbad" zamiast "data" i podaje wartość w kolumnie 7.
    Nie wiem dlaczego tak się dzieje :/
    Załączyłam plik i kolorem wskazałam dane linijki.

    0
  • #25 10 Gru 2014 21:58
    adamas_nt
    Moderator Programowanie

    Naprawdę ja to pisałem? Przecież można prościej ;)

    Edit: Zjadłem zerowanie wiersza. Załącznik zmieniłem.

    Sprawa oznaczeń. Nie przewidziałem, że może być wiele wierszy różniących się tylko ilością (15 i 16 w "forecast"). Jeśli to nie błąd, to przyjdzie "pogrzebać" (znacznik 'chk' w pozostałych gałęziach Select Case) w fazie I...

    1
  • #26 13 Gru 2014 12:51
    maryluu2014
    Poziom 6  

    Dziękuję bardzo za odpowiedź i przepraszam, że dopiero teraz odpowiadam.
    Kod działa super i znacznie szybciej niz poprzedni :), ewelacja. Jedyna rzecz która mnie niepokoi to to, że nie odróżnia wierszy " Qbad/Data" od " do wprowadzenia".
    " do wprowadzenia" powinny byc wiersze dodatkowe-tzn. jeżeli w ark "dane" są 3 wiersze zamówienia PO12345 a w ark"forecast" są 4 wiersze zaczynające się na PO12345 to jeden, ostatni wiersz będzie " do wprowadzenia".
    A jeżeli różnią się tylko ilościa i datą to "Qbad/Data"
    Rozróżnienie to jest bardzo trudne. Czy w ogóle jest to możliwe?

    Dodano po 2 [godziny] 36 [minuty]:

    A może zupełnie zrezygnujmy z "do wprowadzenia " i " do usunięcia", niech będą tylko wiersze zgodne, "qbad" i "data", "qbad/data" i może "brak"- zamówienia które są w ark forecast a ich brak w ark dane. Co o tym sądzisz ? Może tak będzie łatwiej.
    Jak teraz uruchomilam makro to wszystko było "do wprowadzenia".
    Może ten drugi sposób będzie lepszy. Czekam na twoją opinię

    0
  • #27 13 Gru 2014 20:46
    adamas_nt
    Moderator Programowanie

    Mamy tu sytuację, gdzie wiele rekordów, pasuje do wielu. Rozpatrzmy wszystko od końca.
    Faza III: możemy po prostu zliczyć ilości po obu stronach (podobnie jak w fazie II) i oznaczać "do wprowadzenia" wiersze "jak leci". W obecnym kształcie, makro szuka najmniej dopasowanych w zbiorze (patrz kolejność przypisania zmiennej 'ar')* Być może trzeba dołożyć/zmienić warunki (if) w selectach fazy I...

    Faza II: tu nie powinno być problemów, makro zlicza i oznacza nadmiar.

    Faza I: musisz jakoś "dostroić" makro. Kluczowe jest wykluczenie lub nie rekordu z następnych prób porównań (znacznik 'chk').
    Moje założenie: Jeśli w porównaniu do pierwszej frazy, makro "uzna" że jest różna Np data i ilość, to przy porównaniu do następnej frazy (elementu tablicy unikatów) trzeba "dać mu szansę zmiany decyzji", jeśli wiersz będzie różnił się jedynie, przypuśćmy, datą. Ma to znaczenie w przypadku realizacji założenia opisanego wyżej (to z *).
    Jeśli uprościmy fazę III, w każdej gałęzi Select można śmiało oznaczać wiersz jako "sprawdzony, nie podlegający dalszym porównaniom) 'wkSrc.Cells(wrsDane, 6) = "chk". Moim zdaniem, nie będzie to jednak optymalne.

    Nie znam priorytetów w Twoim założeniu. Jeśli liczy się jedynie uzgodnienie ilości rekordów bez większego zawracania uwagi na ilość i datę, a takie odnoszę wrażenie po twojej ostatniej wypowiedzi, to cała faza I jest zbędna.

    Przemyśl sprawę raz jeszcze. Nie wiem skąd się biorą dane, do czego się odnoszą, ani jakie znaczenie mają tu ilości i daty (kto lub co powoduje niezgodności, które są realne/prawdziwe). Może rzeczywiście wszystko jest okropnie proste, tylko sami sobie to skomplikowaliśmy ;)

    0
  • #28 14 Gru 2014 21:19
    maryluu2014
    Poziom 6  

    Sądzę, że sama wszystko skomplikowałam. Daty i ilości są bardzo istotne - oznaczają ile danych części(material no) z danego zamówienia ma zostać dostarczonych do klienta w określonym dniu( data). Ark 'Dane" to stan systemu, ark "forecast" to zmiany w zamówieniu jakie wprowadza klient.
    Może wystarczyłoby po prostu gdyby makro działało w ten sposób:
    jeżeli
    -kolumny ABCD w ark dane= ark Forecast ABCD to "OK"
    -ark dane ABCD<>ark forecast ABCD to "do wprowadzenia"
    Zakładając że pozostałe kolumny są sobie równe:
    -ark dane BCD<>ark forecast BCD to też " do wprowadzenia"

    - ark dane CD<>ark forecast CD to " qbad/Data" + wypisanie ilości i daty

    - ark dane C<>ark forecast C to " qbad" + wypisanie ilości
    - ark dane D<> ark forecast D to " Data" + wypisanie daty

    Rozumiem, ze jeden wiersz z ark dane oznacza jeden wiersz z ark forecast(?)
    Wtedy, przy różnej ilości wierszy- gdy w ark forecast będzie np o jeden wiersz więcej( tego samego zamówienia)j to nie zostanie on oznaczony- pozostanie bez żadnego komunikatu, i wszytskie takie wiersze też " do wprowadzenia".

    Może w ten sposób będzie prościej, jak sądzisz?

    0
  • #29 14 Gru 2014 22:38
    adamas_nt
    Moderator Programowanie

    Chyba wiem gdzie "pies pogrzebany". Musimy znacznik rekordu umieszczać w obu arkuszach. A w "dane" najlepiej w postaci liczby (zamiast chk). Wtedy wewnątrz selectów możemy sprawdzić priorytet. Np
    4-OK
    3-niezgodne data (tu trzeba się zdecydować)
    2-niezgodna ilość
    1-niezgodne ilość oraz data
    Wtedy

    Kod: vb
    Zaloguj się, aby zobaczyć kod
    i powinno być dobrze.
    Jutro spróbuję to przetestować w praktyce. Jeśli nam się to sprawdzi, druga i trzecia faza to już "pryszcz".

    Edit: w załączniku wyniki po fazie 1+2. W zasadzie puste wiersze w "forecast", to "do wprowadzenia".

    0
  • #30 15 Gru 2014 10:15
    maryluu2014
    Poziom 6  

    Dziękuję :). Makro działa dużo lepiej-wskazuje realne błędy i nie oznacza wszystkich wierszy " do wprowadzenia". Mam jednak jedną uwagę - nie porównuje wierszy 1:1-czyli, mając jeden wiersz w ark dane z identyczna kolumną A i B, a 2 takie wiersze w ark forecast, oznacza dwa wiersze w ark 2 ( jeden wiersz jako OK drugi- qbad/data)-porównuje ten jeden wiersz do dwóch i je oznacza, powinien oznaczyć tylko jeden, a drugi zostawić pusty. Tak sądzę.
    Plik wysłałam na pw

    0
  Szukaj w 5mln produktów