Excel Help

[Fighter of Foo]

I'm trying to format some data I copied off a website.

I know the company names, as well as the rest of the data, are in certain cells at certain intervals. So using company name as the example, the companies are in cells A1, A6, A11, A16... I'm taking these values and putting them in a separate column. When I fill down I get A1, A2, A3 which doesn't help and when I pull down on the right corner, it repeats the sequencing fillin in A5, A10, A15, A20 instead of continuing the pattern.

Hope this makes sense.

Any suggestions?

[Pumpy Tudors]

I'm not sure exactly what kind of result you're looking for. Do you want the company names to be in B1, B6, B11, B16...? I would think you're looking to do something a little more complex than that, but I'm not sure what it is.

[Fighter of Foo]

The end result is that all the company names go in column G or whatever.
Addresses in H, City in I and so on.

[Pumpy Tudors]

Oh, I see. Yeah, that one's pretty tricky, and I can't think of an easy way to do it at all. :(

[PineTar]

Assume the following: every record contains 5 rows of data and this data resides entirely in Column A.

Cell A1: Company Name
Cell A2: Address Line 1
Cell A3: City
Cell A4: State
Cell A5: Zip
Cell A6: Comany #2 Name
Cell A7: etc........

Use the following formulas
Cell G1: "=A1"
Cell H1: "=A2"
Cell I1: "=A3"
Cell J1: "=A4"
Cell K1: "=A5"

Now highlight cells G1 thru K5 and hit CTRL-C
Go to Cell G6 and hit CTRL-V

Rinse and repeat every 5 rows down.

When you are done, copy Columns G through K to a new worksheet and Paste >>> Special >>> Values

Now sort on this selection to move all the blank lines to the end.

[Fighter of Foo]

Thanks guys. I ended up getting impatient and doing a copy, paste special, transpose which I think does the same thing as above.

I now remember why I typically pay someone to scrape and format this stuff properly. :)

[RPI-Fan]

I have a spreadsheet with ~300 data points in it. For each data point, I know how many facilities are within 0-500, 500-1000, 1000-2000, etc. feet of it (but no more specific than that).

If I want to calculate the average distance to the 5 nearest facilities, how can I do that? Sometimes the 5 nearest might be in the 500-1000 range, sometimes the 1000-2000, sometimes a mix of both, etc. There are (way) more than 5 facilities total within x distance of each data point (i.e. I will need to ignore a bunch of facilities for each data point).

I know I have to make some assumptions (like 0-500 = 250'... no need to randomize variables, etc.).

Any ideas?

[SteveMax58]

I have a spreadsheet with ~300 data points in it. For each data point, I know how many facilities are within 0-500, 500-1000, 1000-2000, etc. feet of it (but no more specific than that).

If I want to calculate the average distance to the 5 nearest facilities, how can I do that? Sometimes the 5 nearest might be in the 500-1000 range, sometimes the 1000-2000, sometimes a mix of both, etc. There are (way) more than 5 facilities total within x distance of each data point (i.e. I will need to ignore a bunch of facilities for each data point).

I know I have to make some assumptions (like 0-500 = 250'... no need to randomize variables, etc.).

Any ideas?

So...do you have a matching top row/first column of facility names or something like that? And in this grid, the approximate distance from each to each is listed?

And you want to find the 5 closest, in each row, and average the approx distances to them in a cell at the end of the row? Do you also want to have a named readout of these facilities at the end of each row/column?

Let me know...this doesnt sound too bad...but I might be assuming too much on your data layout & purpose.

[RPI-Fan]

<table x:str="" style="border-collapse: collapse; width: 208pt;" border="0" cellpadding="0" cellspacing="0" width="277"><col style="width: 48pt;" span="2" width="64"> <col style="width: 53pt;" width="71"> <col style="width: 59pt;" width="78"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" height="17" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">0-500 ft</td> <td class="xl24" style="width: 53pt;" width="71">500-1000 ft</td> <td class="xl24" style="width: 59pt;" width="78">1000-2000 ft</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">point 0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">4</td> <td class="xl24" x:num="">12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">point 1</td> <td class="xl24" x:num="">2</td> <td class="xl24" x:num="">2</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">point 2</td> <td class="xl24" x:num="">3</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">7</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">...</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">point 295</td> <td class="xl24" x:num="">1</td> <td class="xl24" x:num="">1</td> <td class="xl24" x:num="">6</td> </tr> </tbody></table>

So that means, for point 0, no facilities are 0 - 500 ft away, 4 facilities are b/w 500-1000 ft away (so assume 750 ft), and 12 facilities are 1000 - 2000 ft away (so assume 1500 ft). But, I only want to count the nearest 5 facilities. So for that one, it would be (4*750 + 1*1500)/5. But I have NO clue how to write that in a formula.

For the record, I have 7 columns of distance "ranges" (as opposed to the 3 in the example above). I'm thinking about adding more ranges to get better accuracy (also for the record... I'm assuming nobody is familar with ArcGIS... the tool that is supposed to calculate this form me in GIS doesn't work because one of my data sets is in a geographic coordinate system... any ideas?).

Also, I would just do this by hand, but I might have to do this process for other locations (or at least, somebody might have to do it).

Right now, I'm thinking that when I get a few hours free I will just code it in VB. But I always like learning new formulas tricks, so any advice would be greatly appreciated.

[SteveMax58]

OK...this is a bit lengthy, but it's the best I have after being up since 3am. Given all the potential variation, I needed to basically "IF" the heck out of this thing. :)

Assuming your first "data point" cell is B2:

=IF(B2>=5,5*250,B2*250+IF(B2+C2>=5,(5-B2)*750,C2*750+IF(SUM(B2:D2)>=5,(5-B2-C2)*1500,D2*1500)))/IF(SUM(B2:D2)<5,SUM(B2:D2),5)

Not sure if this is a concern, but I made an assumption you'd rather not have the average divided by 5, if there is not a total of 5 facilities. This may never be the case, but it does account for that possibility, FWIW.

If you do add more columns to further parse out the distances, just rinse & repeat, or ask & I'll add the amount of columns to it for you. If you want to make the average criteria(5) selectable, you could make a dropdown at the top with different options as well & just replace the 5's with that cell reference.

[RPI-Fan]

Thanks!!

I'll test it out tomorrow and report back. Much appreciated.

[RPI-Fan]

Actually looking at it, I think I need to add in a max(0,5-B2-C2) to make sure that it doesn't do any negative multiplication?

[SteveMax58]

I dont think you should have to modify it, other than the exact cell referencess you're using in your sheet. If you take the part that you pulled out:

IF(SUM(B2:D2)>=5,(5-B2-C2)*1500,D2*1500)

Earlier in the formula(to the left), we already established B2 & C2 are not greater than or equal to 5, so it is basically just trying to find out how many sites it needs from D2.. So the IF statement says...IF B2 + C2 + D2 is greater than or equal to 5, take 5 -B2 & C2 and the leftover is how many D2 sites to use.

I tried it out in a test workbook, and it calculated correctly for every scenario I could think of. Let me know if you want me to send you the workbook to see if there is anything different about it compared to your dataset.

One thing I forgot to mention...if you parse your sites out further, this formula relies on the site distances to be in ascending order, from left to right.

Hope that helps.