Hi everyone,
This is a recurring problem that's been bugging me for a while (well, 1.5 problems). I have a list of about 200 values that vary from anywhere from 1-15 entries per value. To clarify let's say it looks like this:
1) Problem 1: Assume you have this dataset:
A 3
A 2
A 6
B 1
C 9
C 11
C 4
D 8
etc.
Now I'm trying to create separate worksheets for each of these, where it would show:
A 3
A 2
A 6
on one, then afterwards
B 1
on the other etc.
I have been using vlookup tables, but the problem is since I don't know how many formulas I use per page, I always end up with a string of 0s afterwards. So the data ends up looking like:
A 3
A 2
A 6
0 0
0 0
0 0
and
B 1
0 0
0 0
etc.
Having to manually fill-down the exact amount of entries on each one is out of the question because I have hundreds of entries for each and this needs to be done every week. Since this is a recurring, random data-set though, I have to include the maximum amount of =vlookup formulas to make sure they're all included in the separate reports.
Anyone know how I can make it so the values are included If and only if they do not equal 0, otherwise it's a blank space?
(Problem 2) From the raw data, i want to create a final set of the sum of each of these, without any space in between. so for example:
A 2
A 1
A 9
C 1
F 2
etc.
So notice there is no B, D or E. However since I'm using lookup tables, I end up with this Summary Chart:
A 12
B 0
C 1
D 0
E 0
F 2
It looks very sloppy with all of those empty 0s. Any way I can clean it up and make it so that only the letters with actual values appear, without any spaces in between?:
A 12
C 1
F 2
Thanks so much in advance!
This is a recurring problem that's been bugging me for a while (well, 1.5 problems). I have a list of about 200 values that vary from anywhere from 1-15 entries per value. To clarify let's say it looks like this:
1) Problem 1: Assume you have this dataset:
A 3
A 2
A 6
B 1
C 9
C 11
C 4
D 8
etc.
Now I'm trying to create separate worksheets for each of these, where it would show:
A 3
A 2
A 6
on one, then afterwards
B 1
on the other etc.
I have been using vlookup tables, but the problem is since I don't know how many formulas I use per page, I always end up with a string of 0s afterwards. So the data ends up looking like:
A 3
A 2
A 6
0 0
0 0
0 0
and
B 1
0 0
0 0
etc.
Having to manually fill-down the exact amount of entries on each one is out of the question because I have hundreds of entries for each and this needs to be done every week. Since this is a recurring, random data-set though, I have to include the maximum amount of =vlookup formulas to make sure they're all included in the separate reports.
Anyone know how I can make it so the values are included If and only if they do not equal 0, otherwise it's a blank space?
(Problem 2) From the raw data, i want to create a final set of the sum of each of these, without any space in between. so for example:
A 2
A 1
A 9
C 1
F 2
etc.
So notice there is no B, D or E. However since I'm using lookup tables, I end up with this Summary Chart:
A 12
B 0
C 1
D 0
E 0
F 2
It looks very sloppy with all of those empty 0s. Any way I can clean it up and make it so that only the letters with actual values appear, without any spaces in between?:
A 12
C 1
F 2
Thanks so much in advance!
