excel 101

I am in the midst of finishing a report for my users specially for whatever mgmt/exco/board/mcmc reporting and etc. This is my first time doing so for this user group coz I just got assigned to it came early sept ago. The report contains a seperate 5 criteria which took me about 3 days to get the data alone. Every criteria contains on average of 80000+ rows of data set. Did u know each excel file that u open can only cater for 65000 rows? so I have to load the data set into seperate worksheet and later kena buat seperate pivot on those sheets.

After the pivot, kena combine the summary sheet into one, and then its time to further massage the data and it goes round and round for all the rest of 5 criteria. I am now on my last criteria. I am at bottle neck after my formula doesnt work. Few times of trying until i give up and get back to my developer. Aghhh I would never think of this.

Kak Elin, Tricknya kena add 5 blank rows kat atas (insert row), kemudian kat cell K7, masukkan formula:

=IF(J7J6,”1st time”,IF(COUNTIF(J1:J6,J7) >3, “Re-appt >3”,”Re-appt “&COUNTIF(J1:J6,J7)))

Copy to all cells in column K, and then delete the 5 blank rows.

Aghhh kejadah betul, mana ader org main2 trick gini. I feel like screaming. Separuh gile la nak buat keje ni. And to think that I have to go thru this every month. No wonder the person who did this before said, it can be very technical, tricky and sakitkan hati when u do the reporting. Mata obviously juling, and it will take the fastest 3 days to complete.

MMg pun, I am at the neck of it now. Cepatla habis yg ni. I am still copying the formulae to my others 45000 rows and lps tuh massaging stage. Then barulah I can start on the real report based on all this summary table. pening kepala mak enon. tension ok.

I need a good lasagna fix this afternoon and a cuppa. Sapa kat building ni nak join mak lunch kat SFCoffee around 12ish mohlah jumpa kat sana nanti yerrrr…

Advertisements

7 Responses to “excel 101”

  1. Ummu Auni Says:

    ala baru baca, kalau tak, boleh join…huhuhu

  2. zatul Says:

    *gulp* byknya!!!!
    byk2 la bawak sabar ye…

  3. mommy Says:

    OMG..that’s too complicated. u deserve a big break! happy weekends.

  4. Kopi Suam Says:

    akak dah merasa ni dulu masa buat data conversion and data cleansing for our SAP training module…confirm sampai nak pitam dan juling bijik mata…argghhhhh serius nak muntah

  5. aisha Says:

    kak elin, that formula tuk ur report ke boleh apply for any excell if the data more than 65000? or that formula for what puspose? share aci?

  6. mimielola Says:

    You know, Elin, I love Excel. Tiap2 tahun I hantar nama I utk pi kursus Advance Excel – tp tak pernah nak dpt.

    So skrg ni, I hanya guna excel utk tabulated data & basic summation aje!!! Haha, am so pathetic

  7. the cheeky wife Says:

    elinn…i tak perasan ur comment yesterday at the AH entry….tu pun nasib baik hubby cakap. dah reply

    anyway, check out AH official website and look for the TARGET link…tp semua udah sold out

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: