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

Adding Client Name to first Column from second column

Cbronkie

New Member
Good morning to all,

I receive large payroll files with hundreds of clients with some have nearly a million lines of data (small sample attached) and I need to be able to sort and Pivot the data. The problems is the client name is on the same line as the addresses and employees. How do I get the client name to column 1 and align with the data for each client. My sample shows the date I receive in column 2 and the way I need it in column I?
 

Attachments

  • Excel sample .xlsx
    10.5 KB · Views: 9
Last edited:
Cbronkie
... with hundreds of clients with some have nearly a million lines of data ...
I would recommend to do those like this sample ... press [ This way ]-button
... instead of formulas.
 

Attachments

  • Excel sample .xlsb
    17.2 KB · Views: 11
Cbronkie
... with hundreds of clients with some have nearly a million lines of data ...
I would recommend to do those like this sample ... press [ This way ]-button
... instead of formulas.
Vletm,
Thanks, but this is way over my head, how do I apply this to my worksheet?

Thank you
Chris
 
It should be easy using Power Query but I suspect identifying clients be the fact that they start with 'Client' is not good enough :DD
What distinguishes a client row?
Are the blank rows needed?
 
Peter,
I made a typo and should say column not line.
The problems is the client's name is on the same line (column) as the addresses and employees
 
Cbronkie
Maybe Your 'Yes' (#7) was for me ...
Instructions:
1) Right click on [ This way ]-button
2) Assign Macro...
3) Edit
4) Do ... Ctrl+A ... Crtl+C
5) From 'Project - VBAProject'-window (have to be visible)
... click Your files [+] until You can see Your used sheets name (photo - now it's Book1-file's Sheet1-sheet)
Screenshot 2019-12-02 at 14.31.20.png
6) Click that sheet1
7) Move mouses cursor to that opened Sheet1 (Code) and Do Ctrl-V
8) Delete text Private from the 1st line
9) Close my made file
10) Save Your file with extension .xlsb ( ~uses Macros)
11) Now, it's ready to run
... It will OVERWRITE A-column ... so, have a backup before You're sure what are You doing!
 
Cbronkie
Maybe Your 'Yes' (#7) was for me ...
Instructions:
1) Right click on [ This way ]-button
2) Assign Macro...
3) Edit
4) Do ... Ctrl+A ... Crtl+C
5) From 'Project - VBAProject'-window (have to be visible)
... click Your files [+] until You can see Your used sheets name (photo - now it's Book1-file's Sheet1-sheet)
View attachment 64302
6) Click that sheet1
7) Move mouses cursor to that opened Sheet1 (Code) and Do Ctrl-V
8) Delete text Private from the 1st line
9) Close my made file
10) Save Your file with extension .xlsb ( ~uses Macros)
11) Now, it's ready to run
... It will OVERWRITE A-column ... so, have a backup before You're sure what are You doing!
Yes, used the wrong reply. Thanks this will be a big help going forward.
 
Yes, used the wrong reply. Thanks this will be a big help going forward.
I was lost at ... click Your files [+] until You can see Your used sheets name (photo - now it's Book1-file's Sheet1-sheet). I copied and pasted your "This Way" to my work file and it worked.
 
Back
Top