• 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...

  • 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

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
 

Attachments

  • Excel Data- Locaition, Dept and Client Wise 11.06.2018.xlsx
    12.2 KB · Views: 5
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)
 

Attachments

  • Copy of Excel Data- Locaition, Dept and Client Wise 11.06.2018.xlsx
    24 KB · Views: 4
Last edited:
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)

Sir, you are ultimate. This worked for me.
 
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?
 
Back
Top