Excel help - comparing lists

[cowgirl836]

I think an IF formula will only be part of it since I can use that for the cutoff point. But I'm just trying to figure out how to create some "entry cells" where I would enter said data and once I hit a created "Submit" button, it would automatically put that information into one of two tables. I have a friend that programs for IBM and he says it's possible, but I hate to put this on him when he has his own work to do.

so you do have a cutoff point so you could be like "=IF((A1>10, B1),B2))" where A1 is the cell you are entering data into, B1 is the column for data points greater than 10, B2 is less than. But yeah, if you just want one entry cell (which is logical), I'm not sure how to do that.

 

[GMackey32]

so you do have a cutoff point so you could be like "=IF((A1>10, B1),B2))" where A1 is the cell you are entering data into, B1 is the column for data points greater than 10, B2 is less than. But yeah, if you just want one entry cell (which is logical), I'm not sure how to do that.

There's 4 columns of data: Discard Box #, Crop, Weight (lbs), and File name and/or Description. The IF statement would be based on the weight so if its less than or equal to 200, goes to one table. Greater than 200, goes to the other table. The macro would have to run with a button (which isn't hard to create) so that it doesn't run as you are still entering the data.

I figured you should be able to have one data entry location and from there, the macro would sort to one table or another.

 

[IAmNotTheMayor]

I typically to use the COUNTIF() function for this. Although not necessary, I also create data tables to make it easier. Tables are pretty much awesome. You should use them if you don't already.

1. Hightlight all the cells you want to consider in the first tab and then click Insert>Table
2. Do the same thing for the other tab. If you don't change the names, they should be called Table1 and Table2.
3. On the first table, insert a new column. Insert this formula: =COUNTIF(OppositeTableName[column searching against], [cell in current table to match]). The table should automatically copy the formula down for all rows.
4. If there is a match, it will be 1 (one/true), if not, it will be 0 (zero/false).
5. Go to the other table, and invert the formula.

 

[cowgirl836]

There's 4 columns of data: Discard Box #, Crop, Weight (lbs), and File name and/or Description. The IF statement would be based on the weight so if its less than or equal to 200, goes to one table. Greater than 200, goes to the other table. The macro would have to run with a button (which isn't hard to create) so that it doesn't run as you are still entering the data.

I figured you should be able to have one data entry location and from there, the macro would sort to one table or another.

stealing from your FB comments, but you need Access. It can probably be done in Excel, but it can probably be more easily done and stored in Access. Since I'm assuming you probably then want to link these numbers back to a specific seed/field/whatever and then you could have a couple nice reports of the above and below 200s.

 

[clonz]

stealing from your FB comments, but you need Access. It can probably be done in Excel, but it can probably be more easily done and stored in Access. Since I'm assuming you probably then want to link these numbers back to a specific seed/field/whatever and then you could have a couple nice reports of the above and below 200s.

You could use Access, but you definitely don't need to. I would do a sort (select all of your data columns) by the weight column and then you could just cut the section above and below 200 and paste. I know you want this automated and you can definitely automate it, if your wiling to look up a little vb for apps. It shouldn't take more than 10 lines of code. If you want to do it that way and need more help I can look it up quick, I have done this same sort of thing before.

 

[GMackey32]

You could use Access, but you definitely don't need to. I would do a sort (select all of your data columns) by the weight column and then you could just cut the section above and below 200 and paste. I know you want this automated and you can definitely automate it, if your wiling to look up a little vb for apps. It shouldn't take more than 10 lines of code. If you want to do it that way and need more help I can look it up quick, I have done this same sort of thing before.

I'm basically going to be adding information as I go, over the course of weeks. So basically I would have to have a section where I would input data (4 columns wide) and once I hit the "insert" or "submit" button, it would auto file them into the correct table by a cutoff weight and then also auto sort them within their respective table by ascending weight.

 

[GMackey32]

I was able to push this project to Access if that makes it any easier

 

[cowgirl836]

I was able to push this project to Access if that makes it any easier

My Access expert is on vacation.....I'm good at making reports in there but not what you need! Their help forums have been useful in the past if you can put a name to what it is you're trying to do.

 

[besserheimerphat]

I was able to push this project to Access if that makes it any easier

I think this should be much easier in Access - one big table with all of your columns, then set up a query that pulls and organizes it the way you want. In Access the raw data always stays in one table, and the query results are displayed however you specify them.