A big lookup task

Jumparound

New Member
Joined
Aug 4, 2015
Messages
39
Office Version
  1. 2016
Platform
  1. 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
ABDOQYAA
1Order QuantitySales unitCustom.Mat.Descrip.Sold-to partySales Doc.delivery dateTotal ZPRF
21PieceDry Mixed Recycling (REL)Joe Bloggs145601/01/201641
30PieceHaulageAN Other132515/12/20157
41drumAcidsAN Other124505/09/20163
51IBCAcidsAN Other124505/09/20162.95
6125AcidsAN Other124505/09/20162.95
715AcidsAN Other124505/09/20161.5
815AcidsAN Other124505/09/20163
915AcidsAN Other124505/09/20162.95
1015AcidsAN Other124505/09/20162.95
1115AcidsAN Other124505/09/20162.95
1215AcidsAN Other124505/09/20162.95
13125AcidsAN Other124505/09/20162.95
1415AcidsAN Other124505/09/20164
15125AcidsAN Other124505/09/20168
161PieceAdministration (03/12/15)BCB145601/01/201418
171PieceAdministration (04/12/15)BCB145601/01/201418
181PieceAdministration (07/12/15)BCB145601/01/201418

<tbody>
</tbody>
Raw



My sheet 2 would look something like this with this customer typed into C3

Excel 2010 32 bit
CDEFGHI
2Customer NameNumber of sales ordersDate Last Sales OrderLast Sales Order NumberValue last sales orderTotal value all sales orders
3AN Other205/09/2016124540.1547.15
4
5
6
7Order QuantitySales unitCustom.Mat.Descrip.Sold-to partySales Doc.delivery dateTotal ZPRF
80PieceHaulageAN Other132515/12/20157
91drumAcidsAN Other124505/09/20163
101IBCAcidsAN Other124505/09/20162.95
11125AcidsAN Other124505/09/20162.95
1215AcidsAN Other124505/09/20161.5
1315AcidsAN Other124505/09/20163
1415AcidsAN Other124505/09/20162.95
1515AcidsAN Other124505/09/20162.95
1615AcidsAN Other124505/09/20162.95
1715AcidsAN Other124505/09/20162.95
18125AcidsAN Other124505/09/20162.95
1915AcidsAN Other124505/09/20164
20125AcidsAN Other124505/09/20168

<tbody>
</tbody>
Sheet2
(Again this has several columns hidden for this forum)

Hope you can help! Thanks
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Any suggestions? I'm struggling with it and think the only solution is going to be some vba. Which is beyond my abilities.
 
Upvote 0
Hi, the number of unique values in column Q. Each sales order has a unique number to identify it
 
Upvote 0
ADVERTISEMENT
Hi, the number of unique values in column Q. Each sales order has a unique number to identify it

In C4 of Sheet2 enter:

=COUNTIFS(Sheet1!O2:O18,C3)

In B8 of Sheet2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$8:B8)>$C$4,"",SMALL(IF(Sheet1!$O$2:$O$18=$C$3,ROW(Sheet1!$O$2:$O$18)-ROW(Sheet1!$O$2)+1),ROWS($B$8:B8)))

In C8 just enter, copy across, and down:

=IF($B8="","",INDEX(Sheet1!$A$2:$AA$18,$B8,MATCH(C$7,Sheet1!$A$1:$AA$1,0)))

Now w have two options for determing the values of D3:H3 of Sheet2. (a) Use the data in Sheet1 or (b) the results just created in B:I in Sheet2. What follows uses option (a).

In D3 control+shift+enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(Sheet1!Q2:Q18),IF(Sheet1!O2:O18=C3,Sheet1!Q2:Q18)),Sheet1!Q2:Q18),1))

In E3 just enter:

=LOOKUP(9.99999999999999E+307,1/(Sheet1!O2:O18=C3),Sheet1!Y2:Y18)

In F3 just enter:

=LOOKUP(9.99999999999999E+307,1/(Sheet1!O2:O18=C3),Sheet1!Q2:Q18)

In G3 just enter:

=SUMIFS(Sheet1!AA2:AA18,Sheet1!Q2:Q18,F3)

In H3 just enter:

=SUMIFS(Sheet1!AA2:AA18,Sheet1!O2:O18,C3)
 
Upvote 0
Wow that's amazing thank you! Is there any way however to get it to work based on a partial match? It only currently works on an exact match.
 
Upvote 0
ADVERTISEMENT
Wow that's amazing thank you! Is there any way however to get it to work based on a partial match? It only currently works on an exact match.

Incredible. You don't even provide any example...
 
Upvote 0
Sorry? An example of a partial match? Well for the above an example would be AN Oth which would find the data for AN Other. Company names can end in for example plc or have other bits in them that the person looking up might not get exactly right. So I wanted to know if there was a way for it to work based on the search term being partially right.
 
Upvote 0
Sorry? An example of a partial match? Well for the above an example would be AN Oth which would find the data for AN Other. Company names can end in for example plc or have other bits in them that the person looking up might not get exactly right. So I wanted to know if there was a way for it to work based on the search term being partially right.

In C4 of Sheet2 enter:

=COUNTIFS(Sheet1!O2:O18,"*"&C3&"*")

In B8 of Sheet2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$8:B8)>$C$4,"",SMALL(IF(ISNUMBER(SEARCH($C$3,Sheet1!$O$2:$O$18)),ROW(Sheet1!$O$2:$O$18)-ROW(Sheet1!$O$2)+1),ROWS($B$8:B8)))

In C8 just enter, copy across, and down:

=IF($B8="","",INDEX(Sheet1!$A$2:$AA$18,$B8,MATCH(C$7,Sheet1!$A$1:$AA$1,0)))

Now we have two options for determine the values of D3:H3 of Sheet2. (a) Using the data in Sheet1 or (b) using the results just created in B:I in Sheet2. What follows implements option (a).

In D3 control+shift+enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(Sheet1!Q2:Q18),IF(ISNUMBER(SEARCH(C3,Sheet1!O2:O18)),Sheet1!Q2:Q18)),Sheet1!Q2:Q18),1))

In E3 just enter:

=LOOKUP(9.99999999999999E+307,SEARCH(C3,Sheet1!O2:O18),Sheet1!Y2:Y18)

In F3 just enter:

=LOOKUP(9.99999999999999E+307,SEARCH(C3,Sheet1!O2:O18),Sheet1!Q2:Q18)

In G3 just enter: (unchanged)

=SUMIFS(Sheet1!AA2:AA18,Sheet1!Q2:Q18,F3)

In H3 just enter: (unchanged)

=SUMIFS(Sheet1!AA2:AA18,Sheet1!O2:O18,C3)
 
Upvote 0

Forum statistics

Threads
1,196,252
Messages
6,014,265
Members
441,809
Latest member
pawansher2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top