[Scott]

Ok, so I have a spreadsheet that has data pulled from another program. The data essentially displays like this:

Column A=Customer Name
Column B=Text Description
Column C=Category Name

The data shows the same customer on multiple lines if they have multiple ads with us, so the data could display in the rows like this...

1. Customer A
2. Customer A
3. Customer B
4. Customer C
5. Customer C
6. Customer C
7. Customer D
8. Customer E
9. Customer F
10. Customer G

Anyway, I need to do two things.

1. Export all the customer data into individual Excel files. One for each unique customer in Column A (I did this already via macro)

2. Upon exporting to all the individual files, reformat so that it looks prettier (This I can't figure out)

Essentially what I want to do is have Column A as a header of a table in each individual excel file that is created, and the Column C & Column B beneath. So merge all the duplicates of Column A into a header of a table when exported, and then list all the ID #'s (Column C) and their accompanying descriptions (Column B)

I'll do a quick paint rendering if that needs to be clarified. :lol:

 

[Scott]

I drew this in paint to illustrate what I want.

I also drew a robot, who is there to point out what it is I want.. As you can tell, he's happy to be able to participate.

 

[Dormant]

2. Upon exporting to all the individual files, reformat so that it looks prettier (This I can't figure out)

Essentially what I want to do is have Column A as a header of a table in each individual excel file that is created, and the Column C & Column B beneath. So merge all the duplicates of Column A into a header of a table when exported, and then list all the ID #'s (Column C) and their accompanying descriptions (Column B)

I'll do a quick paint rendering if that needs to be clarified. :lol:

Sorry Scott, I'm going to ask a potentially stupid question because I don't quite understand your picture :lol: - If the data in Column's B and C is different for each repetition of the customer name in Column A, then surely it would make more sense to just pull the data into a pivot table and then have a summary page with getpivot formulas?

GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,etc)

This would also negate step 1 as if you use the customer name as a column or row field in the pivot table there wouldn't be duplicates.

Sorry if I've totally missed the point, I'm not sure I totally get what you are asking for! :lol:

 

[Scott]

A Pivot Table would work best, and it's what I tried first. However, Column B is text based, and that doesn't translate into a pivot table as text. It transfers over as a number, which can then be clicked on to display the text, but that's not what I need.

Also, Step 1 creates new Worksheets for each unique customer. So while it doesn't merge the duplicates, it at least separates all the customers from each other. This needs to be done for if/when a customer requests their information, I can just send them their individual worksheet. (I would just send it as is, but my boss wants it to be pretty :squint

If worse comes to worse, i'll look in to recording a macro that will format the information in to a table for me, and just run it on an individual, case by case basis when a customer requests their information. But I would have preferred it if it could be done automatically in the same macro as step 1.

And I'm quite sure I'm explaining this poorly, but obviously Excel is not my strong point.

 

[Dormant]

A Pivot Table would work best, and it's what I tried first. However, Column B is text based, and that doesn't translate into a pivot table as text. It transfers over as a number, which can then be clicked on to display the text, but that's not what I need.

You can get a text format if you pull your Column B and C content into the actual column and row fields of the pivot(so either in the left or above the data field). You can then just whack a formula alongside the pivot to pull the data across if a 1 exists in the data source.

Also, Step 1 creates new Worksheets for each unique customer. So while it doesn't merge the duplicates, it at least separates all the customers from each other. This needs to be done for if/when a customer requests their information, I can just send them their individual worksheet. (I would just send it as is, but my boss wants it to be pretty :squint

Sounds ridiculous! :lol: How many customers would you have? Surely far too many to each have their own separate tab?

If worse comes to worse, i'll look in to recording a macro that will format the information in to a table for me, and just run it on an individual, case by case basis when a customer requests their information. But I would have preferred it if it could be done automatically in the same macro as step 1.

And I'm quite sure I'm explaining this poorly, but obviously Excel is not my strong point.

Probably not your fault in explanation to be fair.

On The macro thing - if you hit alt F11 after recording your macro, it will take you to the VBA editor where you can see the actual text code and just copy paste it into your first macro if you want it to do it all at the same time.

I use Excel every day, but I always find it difficult to deal with people's Excel issues unless I can actually see the data. What I would recommend if you use Excel a reasonable amount is go to Microsoft Excel® Training. Excel Templates, Add-ins, Formulas & Macros/VBA and Excel Help and go and sign up on their forum - I have put basic details of a problem up and had brilliant answers from experts the same day. I imagine they will give you more helpful responses than I have so far.

 

[Scott]

You can get a text format if you pull your Column B and C content into the actual column and row fields of the pivot(so either in the left or above the data field). You can then just whack a formula alongside the pivot to pull the data across if a 1 exists in the data source.

This, I don't think I know how to do. :lol:

Obviously I can't post the info here, but I'd be willing to shoot you a PM with more information on what I need if you want to have a look.

Sounds ridiculous! :lol: How many customers would you have? Surely far too many to each have their own separate tab?

258 for the month of October. :lol: (This needs to be done each month)

On The macro thing - if you hit alt F11 after recording your macro, it will take you to the VBA editor where you can see the actual text code and just copy paste it into your first macro if you want it to do it all at the same time.

OK, that's good to know. If I can get that to work, then I'll probably be golden. (I'm expecting to paste it in, and it not work with every customer, or it breaks my macro all together :lol

 

[Dormant]

This, I don't think I know how to do. :lol:

Obviously I can't post the info here, but I'd be willing to shoot you a PM with more information on what I need if you want to have a look.

Cheers Scott - that would obviously be the easiest way, but I have to admit I probably won't have the time anytime soon to look through it as I have just been hit with a giant project at work (doing similar kind of shit :scream hence why it's taken me so long to respond. I really would suggest signing up to ozgrid - it's free - the advice is very good and they are open to any level user.

I just had another thought about what you might be able to do which I might be able to put more time into tomorrow depending on workload.

258 for the month of October. :lol: (This needs to be done each month)

Ouch. Are you sure Excel is the right tool? It sounds to me like Access is what your boss wants - it's form basedand you can pull up records easier. Of course I haven't got a clue how to use it properly so I can't really help there. :lol:

OK, that's good to know. If I can get that to work, then I'll probably be golden. (I'm expecting to paste it in, and it not work with every customer, or it breaks my macro all together :lol

Sorry I couldn't be more helpful but I will see if I can pull something together tomorrow in terms of a potential idea.

 

[Scott]

Cheers Scott - that would obviously be the easiest way, but I have to admit I probably won't have the time anytime soon to look through it as I have just been hit with a giant project at work (doing similar kind of shit :scream hence why it's taken me so long to respond. I really would suggest signing up to ozgrid - it's free - the advice is very good and they are open to any level user.

I just had another thought about what you might be able to do which I might be able to put more time into tomorrow depending on workload.

I'll look at my options over the weekend, and check out ozgrid, but in the mean time I'm gonna have to manually paste the individual customer information in to a template I created.

Luckily, sending this info to customers is on a per request basis, so I don't have to do it with all 300 of them.

Ouch. Are you sure Excel is the right tool? It sounds to me like Access is what your boss wants - it's form basedand you can pull up records easier. Of course I haven't got a clue how to use it properly so I can't really help there. :lol:

I was thinking the same thing, but I don't have Access installed on my computer. (This isn't part of my job description, nor am I paid a premium for doing it. )