Selecting data in Excel with VBA

[celestedeeva]

Hello all,

I will be receiving multiple Excel files - they will all have the same format as to where the data is as follows for each file:

Type 1 Name
12 John
12 Jane
12 Anyone
34 Mike
34 Celeste

Type 2 Name
45 John
45 Jane
45 Anyone
99 Mike
99 Celeste

In the above mentioned I have 5 records for underneath each type - this will be different for all files - some will have more than 5 and some will have less - basically I will not know the total number of records.

I need to create a label for each record that falls underneath each type for all the files in such a way that it will select all records despite number of records and be flexible so that it would accomodate the instances where total records are different for file received.

So if in file 1 I have:

Type 1 Name
12 John
12 Jane
12 Anyone
34 Mike
34 Celeste

Type 2 Name
45 John
45 Jane
45 Anyone
99 Mike
99 Celeste

and in file 2 I have:

Type 1 Name
12 John
12 Jane

Type 2 Name
45 John
45 Jane
45 Anyone

I would need to create a worksheet that would compile all the data as follows for labels:

Type 1 Name
12 John
12 Jane
12 Anyone
34 Mike
34 Celeste
12 John
12 Jane


Type 2 Name
45 John
45 Jane
45 Anyone
99 Mike
99 Celeste
45 John
45 Jane
45 Anyone

Any helpis greatly appreciated.

Celeste.

 

[Gavona]

Must your results be in the same order as the original data?
It would be helpful to indicate what you are going to do with the data next.

i would suggest getting the data into this sort of format:
Type Name spare
Type 1 12 John
Type 1 12 Jane
Type 1 12 Anyone
Type 1 34 Mike
Type 1 34 Celeste
Type 2 45 John
Type 2 45 Jane
Type 2 45 Anyone
Type 2 99 Mike
Type 2 99 Celeste

Then you could use Data subtotals to get this:
Type Name spare
Grand Total 0
Type 1 Total 0
Type 1 12 John
Type 1 12 Jane
Type 1 12 Anyone
Type 1 34 Mike
Type 1 34 Celeste
Type 2 Total 0
Type 2 45 John
Type 2 45 Jane
Type 2 45 Anyone
Type 2 99 Mike
Type 2 99 Celeste

Or a pivot table to get this:
Count of Name
Type Name Total
Type 1
12 Anyone 1
12 Jane 1
12 John 1
34 Celeste 1
34 Mike 1
Type 2
45 Anyone 1
45 Jane 1
45 John 1
99 Celeste 1
99 Mike 1

either of which can be readily manipulated to the format you want. With a pivot table if you have two Type 2 45 Janes in your original data they would NOT be listed separately in the result. If that is not a problem then I would take that approach.

How many files you get, how often you get them, and the expertise on excel will dictate if a vba solution is necessary. For a one-off with 30 files I would just paste them one above the other into a single sheet and use formulae to create the type column, copy, pastespecial, values and a bit of autofiltering or sorting to find the excess rows to delete.


Gavin