Excel wiz needed

[jimbeekeeper]

See pic below, I want to create the table on the right, picking up the data from columns F,G and H and the corresponding date from A

but this data goes on for pages, and I just want to be able to do like a "search" and it makes a nice table like on the right.

Also what happens if the data is not collected on a regular 7 day pattern, a fixed equation would miss a row of data.

So I was just wanting something that scanned down for example column F and when it finds data it moves it across and also it corresponding data.

This then also happens for G and H.


Simples!???

 

[DulwichGnome]

Hi Jim, it's a bit difficult without a copy of the sheet but one way of getting over the 'fix equation' issue might be to have a column next to the data with a 'if' statement in it which puts a 1 into the cell if the cell next to it >0 ie it has something in it. Then sum the column with the 1's and you have how many you need to divide by. If you then hide that column it will look neater.

Mike.

 

[RoofTops]

What are columns B, C & D?

And Pidgeon Watch forums?

 

[jimbeekeeper]

Argh you found me else where?

The figures are not important, what I am trying to do is simpley creat the table on the right with the blanks removed.

People have given me some good exmamples, but as yet none of them update when you ad more data to columns ABCD or E which in turn links to GH or I

 

[nickos]

View attachment 2556

Set up the table as shown.

Enter the formulas in the row 3 cells specified.

When you enter the embedded sumif formulas you ignore the { } brackets and press ctrl shift and enter.

Copy the formulas as far down the spreadsheet as required.

 

[jimbeekeeper]

Thanks nickos

I see what you are doing, but the problem comes when G is not always every 7 days.

I have now created this

For first row

=IF(ROW()-ROW($G$2:$G$9999)+1>ROWS($G$2:$G$9999)-COUNTBLANK($G$2:$G$9999),"",INDIRECT(ADDRESS(SMALL((IF($G$2:$G$9999<>"",ROW($G$2:$G$9999),ROW()+ROWS($G$2:$G$9999))),ROW()-ROW($K$2:$K$9999)+1),COLUMN($G$2:$G$9999),4)))

=IF(ISNA(VLOOKUP($K2,$A$2:$I4560,2,FALSE)),"",VLOOKUP($K2,$A$2:$I4560,2,FALSE))

=IF(ISNA(VLOOKUP($K3,$A$2:$I4561,7,FALSE)),"",VLOOKUP($K3,$A$2:$I4561,7,FALSE))

=IF(ISNA(VLOOKUP($K2,$A$2:$I4560,8,FALSE)),"",VLOOKUP($K2,$A$2:$I4560,8,FALSE))

=IF(ISNA(VLOOKUP($K2,$A$2:$I4560,9,FALSE)),"",VLOOKUP($K2,$A$2:$I4560,9,FALSE))



Simples??

 

[nickos]

the problem comes when G is not always every 7 days.

In that case enter the dates in column G manually and it will work.

 

[barratt_sab]

Another option would be to create a "weeknumber" column, using something like:

=int((A2-date(2010,1,7))/7)*7+date(2010,1,7)

This should provide you with a new column showing the week-beginning date for each data row.

Now you could use the DAVERAGE function or a pivot table to extract and sumarise the data. The DAVERAGE might be a bit easier to set up, but a pivot table would be much more flexible and it can calculate the average for each class and each weeknumber automatically.

 

[jimbeekeeper]

I have progressed to this thus far with vlook up, but having problems in the yellow sections highlighted.

http://uploading.com/files/e516c3d6/lookup%2B2.xls/

 

[barratt_sab]

I appreciate that you may want to continue down the lookup route, but just in case, I attach a version with the new "weekstart" date added (column C on the first page) and with the pivot table in the second sheet. You can see that once you've set the table up, it does all the work for you.

 

[jimbeekeeper]

THanks very much for the time you have spent on it, but I think I have cracked the vlookup sheet and have created quite nice document that looks very much the same as the current manual system, so will not be a major panic for the people entering the data to a sheet that is familiar.

Have a look if you wish at what I have been working on.