Jumparound
New Member
- Joined
- Aug 4, 2015
- Messages
- 45
- Office Version
- 2016
- Platform
- Windows
Hi,
Wondering if you wonderful people can help me out with a little project.
I have a spreadsheet with 30,000 lines+ in a sheet called Raw, these are lists of sales data for orders that we have received.
In sheet 2 I would like to be able to type in to a cell (say C3)the name or part name of a customer and then for it to return:
D3 number of sales orders (number of different values of column Q in Raw)
E3 date of last sales order (latest date from column Y in Raw)
F3 the last sales order number (sales order number from column Q on last date in column Y in Raw)
G3 the value of the last sales order (total of column AA (Raw) in all rows that has sales order in F2)
H3 the total value of all sales orders (total of column AA (Raw) for all rows that have the customer in C3)
I would also like to return all the data from the raw tab that matches the customer name in C3 in sheet 2, from C7 downwards and across.
I hope that makes sense! I've copied some of my raw sheet below but hidden the unimportant columns to save space on screen (total columns go from A to AA). I would like to return all the columns though for the second part of my request.
Excel 2010 32 bit
<tbody>
</tbody>
My sheet 2 would look something like this with this customer typed into C3
Excel 2010 32 bit
<tbody>
</tbody>
Hope you can help! Thanks
Wondering if you wonderful people can help me out with a little project.
I have a spreadsheet with 30,000 lines+ in a sheet called Raw, these are lists of sales data for orders that we have received.
In sheet 2 I would like to be able to type in to a cell (say C3)the name or part name of a customer and then for it to return:
D3 number of sales orders (number of different values of column Q in Raw)
E3 date of last sales order (latest date from column Y in Raw)
F3 the last sales order number (sales order number from column Q on last date in column Y in Raw)
G3 the value of the last sales order (total of column AA (Raw) in all rows that has sales order in F2)
H3 the total value of all sales orders (total of column AA (Raw) for all rows that have the customer in C3)
I would also like to return all the data from the raw tab that matches the customer name in C3 in sheet 2, from C7 downwards and across.
I hope that makes sense! I've copied some of my raw sheet below but hidden the unimportant columns to save space on screen (total columns go from A to AA). I would like to return all the columns though for the second part of my request.
Excel 2010 32 bit
A | B | D | O | Q | Y | AA | |
---|---|---|---|---|---|---|---|
1 | Order Quantity | Sales unit | Custom.Mat.Descrip. | Sold-to party | Sales Doc. | delivery date | Total ZPRF |
2 | 1 | Piece | Dry Mixed Recycling (REL) | Joe Bloggs | 1456 | 01/01/2016 | 41 |
3 | 0 | Piece | Haulage | AN Other | 1325 | 15/12/2015 | 7 |
4 | 1 | drum | Acids | AN Other | 1245 | 05/09/2016 | 3 |
5 | 1 | IBC | Acids | AN Other | 1245 | 05/09/2016 | 2.95 |
6 | 1 | 25 | Acids | AN Other | 1245 | 05/09/2016 | 2.95 |
7 | 1 | 5 | Acids | AN Other | 1245 | 05/09/2016 | 1.5 |
8 | 1 | 5 | Acids | AN Other | 1245 | 05/09/2016 | 3 |
9 | 1 | 5 | Acids | AN Other | 1245 | 05/09/2016 | 2.95 |
10 | 1 | 5 | Acids | AN Other | 1245 | 05/09/2016 | 2.95 |
11 | 1 | 5 | Acids | AN Other | 1245 | 05/09/2016 | 2.95 |
12 | 1 | 5 | Acids | AN Other | 1245 | 05/09/2016 | 2.95 |
13 | 1 | 25 | Acids | AN Other | 1245 | 05/09/2016 | 2.95 |
14 | 1 | 5 | Acids | AN Other | 1245 | 05/09/2016 | 4 |
15 | 1 | 25 | Acids | AN Other | 1245 | 05/09/2016 | 8 |
16 | 1 | Piece | Administration (03/12/15) | BCB | 1456 | 01/01/2014 | 18 |
17 | 1 | Piece | Administration (04/12/15) | BCB | 1456 | 01/01/2014 | 18 |
18 | 1 | Piece | Administration (07/12/15) | BCB | 1456 | 01/01/2014 | 18 |
<tbody>
</tbody>
Raw
My sheet 2 would look something like this with this customer typed into C3
Excel 2010 32 bit
C | D | E | F | G | H | I | |
---|---|---|---|---|---|---|---|
2 | Customer Name | Number of sales orders | Date Last Sales Order | Last Sales Order Number | Value last sales order | Total value all sales orders | |
3 | AN Other | 2 | 05/09/2016 | 1245 | 40.15 | 47.15 | |
4 | |||||||
5 | |||||||
6 | |||||||
7 | Order Quantity | Sales unit | Custom.Mat.Descrip. | Sold-to party | Sales Doc. | delivery date | Total ZPRF |
8 | 0 | Piece | Haulage | AN Other | 1325 | 15/12/2015 | 7 |
9 | 1 | drum | Acids | AN Other | 1245 | 05/09/2016 | 3 |
10 | 1 | IBC | Acids | AN Other | 1245 | 05/09/2016 | 2.95 |
11 | 1 | 25 | Acids | AN Other | 1245 | 05/09/2016 | 2.95 |
12 | 1 | 5 | Acids | AN Other | 1245 | 05/09/2016 | 1.5 |
13 | 1 | 5 | Acids | AN Other | 1245 | 05/09/2016 | 3 |
14 | 1 | 5 | Acids | AN Other | 1245 | 05/09/2016 | 2.95 |
15 | 1 | 5 | Acids | AN Other | 1245 | 05/09/2016 | 2.95 |
16 | 1 | 5 | Acids | AN Other | 1245 | 05/09/2016 | 2.95 |
17 | 1 | 5 | Acids | AN Other | 1245 | 05/09/2016 | 2.95 |
18 | 1 | 25 | Acids | AN Other | 1245 | 05/09/2016 | 2.95 |
19 | 1 | 5 | Acids | AN Other | 1245 | 05/09/2016 | 4 |
20 | 1 | 25 | Acids | AN Other | 1245 | 05/09/2016 | 8 |
<tbody>
</tbody>
Sheet2
(Again this has several columns hidden for this forum)Hope you can help! Thanks