[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

Reformatting excel sheet

Hi,

I have an excel workbook with the following layout on sheet 1 :-

Column A                                      Column B
TownA (001)
A Clients                                           10
T Clients                                             5  
Total Clients                                     15
TownB (002)
A Clients                                            6
B Clients                                            8                                      
G Clients                                             5  
Total Clients                                     19
--------------------
TownC (150)
A Clients                                            3
B Clients                                            4                                      
C Clients                                             6  
Total Clients                                     13

and I need to convert it to on Sheet 2 using a macro

Col A   Col B          Col C         Col D          Col E         Col F        Col G
Code    Name      A Clients    B Clients    C Clients  G Clients  T Clients  
001      TownA         10                                                                       5
002      TownB          6                  8                                    5
-----      ---------          -- ---           ------         -------           ----           -------
150      Town C         3                  4                6



Any help would be much appreciated.

DD.
0
davystocks
Asked:
davystocks
2 Solutions
 
moon_blue69Commented:
its easy to populate the values, can you clarify few point?
1. Where do you get the CODE from?
2. Is T clients the last in the series?

Can the column titles, codes, and the dotted line be pre-populated without the macro?
0
 
mark_harris231Commented:
Presumably, there could be more than Towns A, B and C (with their own unique codes), and that the Clients underneath are not static (e.g., could TownA ever have G Clients?).
0
 
davystocksAuthor Commented:
Sorry I forgot to ask there is a round 400 rows of data in the column.


Column A                                      Column B
TownA (001)
A Clients                                           10
T Clients                                             5  
Total Clients                                     15
TownB (002)
A Clients                                            6
B Clients                                            8                                      
G Clients                                             5  
Total Clients                                     19
TownC (158)
Z Clients                                            6
-------------------- 380 more rows
S Clients                                             5
Total Clients                                      20  
TownZ (150)
A Clients                                            3
B Clients                                            4                                      
S Clients                                             6  
Total Clients                                     13




which  needs to be converted  on Sheet 2 using a macro

Col A   Col B          Col C         Col D          Col E         Col F        Col G  ............ Col J
Code    Name      A Clients    B Clients    C Clients  G Clients  T Clients.......... S Clients  
001      TownA         10                                                                       5
002      TownB          6                  8                                    5
158      Town C         3                  4                6
---100 more rows
150      Town Z          3                  4                                                                                 6


So yes there could be more than Towns A, B, C and also the Clients are not static ( eg TownA could at some point have G clients.
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
Patrick MatthewsCommented:
davystocks,

Should be easy enough to do, but it would be best if you could post a (sanitized) sample file so that we can see exactly how your starting file is laid out.

Patrick
0
 
davystocksAuthor Commented:
Thanks, here you go.
0
 
Patrick MatthewsCommented:
No attachment
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now