Excel Help

[Ben E Lou]

I need to create a second spreadsheet from a single long spreadsheet that, relative to the number of rows, will be sparsely populated in one column. I only want the second spreadsheet to contain data from the first spreadsheet in which Column A is populated. In other words, I want this:

<table border="0" cellpadding="0" cellspacing="0" width="457"><colgroup><col style="mso-width-source:userset;mso-width-alt:4900;width:101pt" width="134"> <col style="mso-width-source:userset;mso-width-alt:7094;width:146pt" width="194"> <col style="mso-width-source:userset;mso-width-alt:4717;width:97pt" width="129"> </colgroup><tbody><tr style="height:18.75pt" height="25"> <td class="xl66" style="height:18.75pt;width:101pt; font-size:14.0pt;color:white;font-weight:700;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#0F243E;mso-pattern:black none" height="25" width="134">AMOUNT</td> <td class="xl67" style="border-left:none;width:146pt;font-size:14.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#0F243E;mso-pattern:black none" width="194">SCHOOL NAME</td> <td class="xl68" style="border-left:none;width:97pt;font-size:14.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#0F243E;mso-pattern:black none" width="129">SCHOOL ID</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none" height="20"> </td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none">ADAK SCHOOL</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none">42886</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20"> </td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext">AKHIOK SCHOOL</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext">42887</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none" height="20">50</td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none">AKIACHAK SCHOOL</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none">42888</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20"> </td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext">AKIAK SCHOOL</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext">42889</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none" height="20">75</td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none">AKIUK MEMORIAL SCHOOL</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none">42973</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20"> </td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext">AKULA ELITNAURVIK SCHOOL</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext">42972</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none" height="20"> </td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none">ALAK SCHOOL</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none">43071</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20">125</td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext">ALAKANUK SCHOOL</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext">42890</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none" height="20"> </td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none">ALLAKAKET SCHOOL</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none">42891</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20"> </td> <td class="xl70" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext">AMBLER SCHOOL</td> <td class="xl71" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext">42892</td> </tr> </tbody></table>

to give me this:

<table border="0" cellpadding="0" cellspacing="0" width="457"><colgroup><col style="mso-width-source:userset;mso-width-alt:4900;width:101pt" width="134"> <col style="mso-width-source:userset;mso-width-alt:7094;width:146pt" width="194"> <col style="mso-width-source:userset;mso-width-alt:4717;width:97pt" width="129"> </colgroup><tbody><tr style="height:18.75pt" height="25"> <td class="xl68" style="height:18.75pt;width:101pt; font-size:14.0pt;color:white;font-weight:700;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#0F243E;mso-pattern:black none" height="25" width="134">AMOUNT</td> <td class="xl70" style="border-left:none;width:97pt;font-size:14.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid windowtext; border-right:none;border-bottom:.5pt solid windowtext;border-left:.5pt solid windowtext; background:#0F243E;mso-pattern:black none" width="129">SCHOOL ID</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none" height="20">50</td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid windowtext; border-right:none;border-bottom:.5pt solid windowtext;border-left:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none">42888</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20">75</td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid windowtext; border-right:none;border-bottom:.5pt solid windowtext;border-left:.5pt solid windowtext">42973</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none" height="20">125</td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid windowtext; border-right:none;border-bottom:.5pt solid windowtext;border-left:.5pt solid windowtext; background:#D9D9D9;mso-pattern:#D9D9D9 none">42890</td> </tr> </tbody></table>

I am creating this for someone else to use, and it needs to be automated, so I can't just delete the blank rows from Column A. I need to actually create a second spreadsheet using formulas from the main 33,000ish-row spreadsheet. Any way to do this?

[Passacaglia]

How automated does it need to be? You could probably create a macro that deletes the blank rows.

[Logan]

Could also Autofilter and uncheck "Show Blanks". Obviously not ideal.

[Passacaglia]

When you say "I can't just delete the blank rows" do you just mean you can't go fishing through 33K rows and deleting them all? Could you just sort Column A so that all the blanks are on top or bottom, then delete them in one shot?

[cartman]

Something like this is much easier to handle in a database than in a spreadsheet. Would it be possible to use Access, and save query results as a spreadsheet?

[Ben E Lou]

When you say "I can't just delete the blank rows" do you just mean you can't go fishing through 33K rows and deleting them all? Could you just sort Column A so that all the blanks are on top or bottom, then delete them in one shot?This is going to be a continual process that a rather luddite client of mine is going to have to do themselves. I don't trust them to sort and delete properly. ;)

Well, and beyond that, the actual second spreadsheet needs to have more fields in it than what I'm showing you--things that I need to set up Excel formulas to create. (The second spreadsheet needs to be "import-ready" with proper ugly-looking field headers to go into their CRM. The first spreadsheet, with the 33,000 schools, needs to be "pretty", as it will be something presented to their donors.)

[Ben E Lou]

Something like this is much easier to handle in a database than in a spreadsheet. Would it be possible to use Access, and save query results as a spreadsheet?No. See the post right below yours. This is the flow:

1. My client emails spreadsheet to donor with Sheet1 active.
2. On Sheet1, Donor enters in amounts of their donation that will be designated to each school, emails spreadsheet back to my client.
3. My client goes to Sheet2 (which is generated only from the rows where "Amount" is populated in Sheet1,) saves as csv, imports into CRM.

[Ben E Lou]

So basically, I need to create something that's extremely simple to use for my client's donors, and very simple to use for my client. ;)

[wade moore]

I pointed Ben to this:

http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx

Should get him started. It's what I've used as a starting point for similar task before.

[Bobble]

Can I add columns to the original data? I'd put a column to the left of "Amount" that was =RANK(B#,B:B) assuming "Amount" is column B.

Then your other spreadsheet is :

=IF(#>COUNT(B:B)," ",VLOOKUP(#,$B$2:$E$100000,3,FALSE))

Where:
# is 1, 2, 3, 4, 5, etc. as you copy down.
B is the "Amount" column
Assuming you want the data in the 3rd column "School Name" in this case.
The " " so it will return a space making the cell look empty.


If I can't add a column, I'm expecting some use of SUMPRODUCT could solve this but I never can figure out SUMPRODUCT.

[Bobble]

Wait, I wedged it all into one formula. No need to add a column to the left of your data. I'm going to assume that your results are starting in row 2 and use ROW()-1 to give me my 1, 2, 3, 4, etc. Obviously that can be adjusted. Here's the formula:

=IF(ROW()-1>COUNT(A:A),"-",VLOOKUP(SMALL(A:A,ROW()-1),$A$2:$C$10000,2,FALSE))

I can offer an explanation of the why's and how's if necessary.

[dunkem]

I think turning on autofilter like Logan said would be your best best. In the first column filter, uncheck the (blanks) box, hit ok, and you should see only the rows with something in the first column. Select all the data (upper left corner), copy & paste into a fresh sheet.