• 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

Last edited:

vletm

Excel Ninja
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

Cbronkie

New Member
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
 

Peter Bartholomew

Well-Known Member
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?
 

Cbronkie

New Member
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
 

vletm

Excel Ninja
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

New Member
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.
 

Cbronkie

New Member
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.
 
Top