1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Replicate data in blank cells

Discussion in 'Ask an Excel Question' started by sureshmandadpu, Jun 11, 2018.

  1. sureshmandadpu

    sureshmandadpu Member

    Messages:
    36
    Dear All,

    I posted my query in the attached file. Can any one please help?

    It should compare Location, Department and Clients and see who has more details and ensure totals are tallied and give one common output which enables to pivot the data.


    Regards,
    M. Suresh

    Attached Files:

  2. vletm

    vletm Excel Ninja

    Messages:
    3,571

    Attached Files:

  3. GraH - Guido

    GraH - Guido Active Member

    Messages:
    663
    Hi,
    would you have Power Query onboard? (Get & Transform?) -> attached file.

    RE-EDIT:
    Manual alternative (EDITED, did not notice the freeze panes and hidden rows):
    In the column B select all the cells, then via GoToSpecial select blanks. Then type in cell B7: = B7 and press CTRL + Enter. It will fill down all blank cells with the preceding values.
    In the columns D&F do the same but use the formula in cell D4: = D5 to "fill up" the values.
    in G4: IF(E4="",C4,E4) and drag down.
    Add column H to correct client list, in H2 type: = IF (LEFT(A4,5)<>"Sales,"",F2)
    Last edited: Jun 11, 2018
    sureshmandadpu likes this.
  4. sureshmandadpu

    sureshmandadpu Member

    Messages:
    36
    Sorry Sir for creating confusion. My sincere apologies.

    In the attached file, I gave data transaction wise. So, we can analyze how output came.

    Attached Files:

  5. sureshmandadpu

    sureshmandadpu Member

    Messages:
    36
    Sir, you are ultimate. This worked for me.
  6. vletm

    vletm Excel Ninja

    Messages:
    3,571
    sureshmandadpu
    Hmm... no answers
    ... but seems that Power Query works.
  7. sureshmandadpu

    sureshmandadpu Member

    Messages:
    36
    Sir, I sent you revised file. Could you please check it?
  8. GraH - Guido

    GraH - Guido Active Member

    Messages:
    663
    Glad you liked it. I assume referring to PQ solution?
  9. vletm

    vletm Excel Ninja

    Messages:
    3,571
    sureshmandadpu
    I checked it twice!
    #4 version has two empty rows between some ... groups.
    and now Client-column is empty...
    I didn't find any answer of my questions:
    Why sometimes 'green' values are from C and sometime from E-column?
    Where comes those 'yellow' values ... from above or below?
    or
    Where did You answer to my questions?

Share This Page