Hey everyone!
So I am trying to write a formula that will extract/pull data from a list/table and sort it based on a company code. What I am trying to do is copy data from a downloaded report (saves as a CSV) and then paste it into a workbook that will automatically sort the data. The data that I am downloading is all the hours we (co-workers and I) have worked for each on of our clients. This data is used to make our invoices that we send to our clients. I already made a workbook that takes all the data and makes the invoice but I find myself spending a lot of time sorting by company then copying and pasting by person. I run this reports each month and there is usually 1700-2000 line items. We have 12 clients and 18 employees so its time consuming to do this each month. Since I can almost guarantee non of that made senses on what I am trying to accomplish, an example is below:
Below is how the data looks from the downloaded report:
<tbody>
</tbody>
This is the table I copy and paste the report into (and how it usually looks):
<tbody>
</tbody>
I don't care if it is any order or not because after I put it in the table I have another spreadsheet that takes this data and puts it into a calendar with the amount of hours worked by person by company and then calculates the amount of hours worked at there rate. Then the invoice spreadsheets uses the data from the calendar spreadsheets to generate the invoices.
All the way I have tried haven't worked out there is like 50000000 rows in-between one row and the next. Also, vlookup does not like the company name and will work for some companies and not others. Index&match works great but I run into the 500000 rows in-between one row and the next problem.
Let me know if you need more info or have questions.
Thanks!
Dalton
So I am trying to write a formula that will extract/pull data from a list/table and sort it based on a company code. What I am trying to do is copy data from a downloaded report (saves as a CSV) and then paste it into a workbook that will automatically sort the data. The data that I am downloading is all the hours we (co-workers and I) have worked for each on of our clients. This data is used to make our invoices that we send to our clients. I already made a workbook that takes all the data and makes the invoice but I find myself spending a lot of time sorting by company then copying and pasting by person. I run this reports each month and there is usually 1700-2000 line items. We have 12 clients and 18 employees so its time consuming to do this each month. Since I can almost guarantee non of that made senses on what I am trying to accomplish, an example is below:
Below is how the data looks from the downloaded report:
| Company | Person | Date | Hours | Rate |
| A | Joe | 06/01/14 | 1 | 100 |
| B | Joe | 06/05/14 | 3 | 120 |
| A | Dan | 06/07/14 | 4 | 140 |
| A | John | 06/01/14 | 2 | 120 |
| C | Jim | 06/10/14 | 6 | 100 |
| D | Dan | 06/12/14 | 5 | 100 |
| A | Joe | 06/10/14 | 2 | 110 |
| C | Dan | 06/12/14 | 3 | 120 |
| D | John | 06/07/14 | 4 | 140 |
<tbody>
</tbody>
This is the table I copy and paste the report into (and how it usually looks):
| A | B | C | D | |||||||||||||||
| Person | Date | Hours | Rate | Person | Date | Hour | Rate | Person | Date | Hour | Rate | Person | Date | Hour | Rate | |||
| Joe | 06/01/14 | 1 | 100 | Joe | 06/05/14 | 3 | 120 | Jim | 06/10/14 | 6 | 100 | Dan | 06/12/14 | 5 | 100 | |||
| Joe | 06/10/14 | 2 | 110 | Dan | 06/12/14 | 3 | 120 | John | 06/07/14 | 4 | 140 | |||||||
| Dan | 06/07/14 | 4 | 140 | |||||||||||||||
| John | 06/01/14 | 2 | 120 |
<tbody>
</tbody>
I don't care if it is any order or not because after I put it in the table I have another spreadsheet that takes this data and puts it into a calendar with the amount of hours worked by person by company and then calculates the amount of hours worked at there rate. Then the invoice spreadsheets uses the data from the calendar spreadsheets to generate the invoices.
All the way I have tried haven't worked out there is like 50000000 rows in-between one row and the next. Also, vlookup does not like the company name and will work for some companies and not others. Index&match works great but I run into the 500000 rows in-between one row and the next problem.
Let me know if you need more info or have questions.
Thanks!
Dalton
