FreakyGirl
Member
Ok I'm stumped.
I have tried several ways to figure this out but with my limited knowledge it’s hard to know if the function I’m using is the best one or even the right one for the job.
I have a column of almost 600 names that changes monthly. I need to take that column & make it readable left to right while separating the different names by letter. WANTED worksheet in the attached workbook shows the end result that I have been doing manually.
I have successfully made the column A read left to right 5 names across then starts over on next line. The problem is when it goes from one letter to the next. If the number of names for a certain letter is not divisible by 5 then I get overflow from the next letter instead of starting a new row.
So I thought if I had 1 column for each letter I could extract the information only from that column. That did the same thing as the across did. So I put in a subtotal to insert a row between the letters. While it’s easier to see where the letters change its still not quite right.
But the subtotal did help when reading left to right as it will show a 0 on the letter change but nothing more. I made several helper columns to try to get x amount of zeros at the end of each row to make it to the next number divisible by 5. For example if there are 27 C names I need to add three 0's for 30 to push the next letter on the next row. Plus add 5 more 0's to give me a blank row when I easily hide the trailing 0's with a conditional. All the formula combos I try don’t seem to do what I need it to.
I have attached a file with 4 worksheets.
MASTER worksheet is main list of names & basic formula for the left to right without the 0's added in. The names just run together.
ABCDE worksheet is the columns of names in abc order. It also has the subtotal line & the names of the helper columns. Anything on this sheet will change the data on the worksheet named final.
WANTED worksheet is what I’m looking to do.
I know how many rows each group of names will take. I know how many zeros I need. I just don’t know how to put it all together. I want to be able to go to FINAL!E2 & put in the 1st letter & have the 5 columns to the right put in the right names because the 1st letter matches. In addition so many rows down from that 1st letter would show the next letter in sequence which would trigger those names to show left to right & so on.
i.e.: 1st letter is "_" & there are 24 names that start with _. That will always be in column E1 on the FINAL worksheet. I want the correct names for that letter to show to the right by 5 columns. When it hits the 25th name there’s a 0 to end the row & 5 more 0's for next row. On the next row at E7 there would be an "A" which happens to be 6 rows down from the previous letter (ABCDE!E2). I have a helper that counts the rows needed but I don’t know how to apply it correctly. Believe me, i've tried.
This would continue until letter Z. Each month as the list changes so would the placement of all the letters depending on the numbers in the helper columns. The "_" letter will always be in E1.
I hope someone can help me solve this & I thank you in advance.
Have a great day!
FreakyGirl
I have tried several ways to figure this out but with my limited knowledge it’s hard to know if the function I’m using is the best one or even the right one for the job.
I have a column of almost 600 names that changes monthly. I need to take that column & make it readable left to right while separating the different names by letter. WANTED worksheet in the attached workbook shows the end result that I have been doing manually.
I have successfully made the column A read left to right 5 names across then starts over on next line. The problem is when it goes from one letter to the next. If the number of names for a certain letter is not divisible by 5 then I get overflow from the next letter instead of starting a new row.
So I thought if I had 1 column for each letter I could extract the information only from that column. That did the same thing as the across did. So I put in a subtotal to insert a row between the letters. While it’s easier to see where the letters change its still not quite right.
But the subtotal did help when reading left to right as it will show a 0 on the letter change but nothing more. I made several helper columns to try to get x amount of zeros at the end of each row to make it to the next number divisible by 5. For example if there are 27 C names I need to add three 0's for 30 to push the next letter on the next row. Plus add 5 more 0's to give me a blank row when I easily hide the trailing 0's with a conditional. All the formula combos I try don’t seem to do what I need it to.
I have attached a file with 4 worksheets.
MASTER worksheet is main list of names & basic formula for the left to right without the 0's added in. The names just run together.
ABCDE worksheet is the columns of names in abc order. It also has the subtotal line & the names of the helper columns. Anything on this sheet will change the data on the worksheet named final.
WANTED worksheet is what I’m looking to do.
I know how many rows each group of names will take. I know how many zeros I need. I just don’t know how to put it all together. I want to be able to go to FINAL!E2 & put in the 1st letter & have the 5 columns to the right put in the right names because the 1st letter matches. In addition so many rows down from that 1st letter would show the next letter in sequence which would trigger those names to show left to right & so on.
i.e.: 1st letter is "_" & there are 24 names that start with _. That will always be in column E1 on the FINAL worksheet. I want the correct names for that letter to show to the right by 5 columns. When it hits the 25th name there’s a 0 to end the row & 5 more 0's for next row. On the next row at E7 there would be an "A" which happens to be 6 rows down from the previous letter (ABCDE!E2). I have a helper that counts the rows needed but I don’t know how to apply it correctly. Believe me, i've tried.
This would continue until letter Z. Each month as the list changes so would the placement of all the letters depending on the numbers in the helper columns. The "_" letter will always be in E1.
I hope someone can help me solve this & I thank you in advance.
Have a great day!
FreakyGirl
