Excel wiz needed

Beekeeping & Apiculture Forum

Help Support Beekeeping & Apiculture Forum:

jimbeekeeper 

Queen Bee
Joined
Dec 14, 2008
Messages
2,454
Reaction score
1
Location
East Yorkshire
Hive Type
national
Number of Hives
1
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 

Field Bee
Joined
May 7, 2009
Messages
534
Reaction score
0
Location
SE London, UK
Hive Type
other
Number of Hives
8 & 5 nucs all Rose
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 

Queen Bee
BeeKeeping Supporter
Joined
Jun 20, 2009
Messages
2,427
Reaction score
0
Location
Kingsbridge, South Devon
Hive Type
none
Number of Hives
0 - Now in beeless retirement!
What are columns B, C & D?

And Pidgeon Watch forums?
 

jimbeekeeper 

Queen Bee
Joined
Dec 14, 2008
Messages
2,454
Reaction score
1
Location
East Yorkshire
Hive Type
national
Number of Hives
1
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 

New Bee
Joined
Jul 3, 2009
Messages
2
Reaction score
0
Location
uk
Hive Type
national
Number of Hives
1
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 

Queen Bee
Joined
Dec 14, 2008
Messages
2,454
Reaction score
1
Location
East Yorkshire
Hive Type
national
Number of Hives
1
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??
 

barratt_sab 

House Bee
Joined
Jun 15, 2010
Messages
275
Reaction score
0
Location
Herts / Essex border
Hive Type
14x12
Number of Hives
12
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.
 

barratt_sab 

House Bee
Joined
Jun 15, 2010
Messages
275
Reaction score
0
Location
Herts / Essex border
Hive Type
14x12
Number of Hives
12
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 

Queen Bee
Joined
Dec 14, 2008
Messages
2,454
Reaction score
1
Location
East Yorkshire
Hive Type
national
Number of Hives
1
THanks very much for the time you have spent on it:hurray:, 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.
 

Latest posts

Top