Match Multiple Items based on Multiple Criteria

[MRobi]

I'm looking for some help on a sales pipeline report I'm working on.

I have 1 worksheet that our sales team can enter their raw data and edit it as they move through different phases of the sales pipeline from Prospect to Quoted and finally to sold. On another worksheet, I'm trying to pull only the sold data in any given month (I have a drop down I can select for example June 2017, Oct 2017, etc).

My main columns are:
A - Pipeline Phase (Prospect, Quoted, Follow Up, Sold, Sold Split)
B - Date (Various months)
C - Company information
D - Sale Type (New Client, Existing Client, National Client)
E - Dollar Amount

So what I'm trying to do exactly is find a formula that will match Pipeline Phase - Sold, Date - Oct, Sale Type - New Client. One it's returned all that move on to Existing Client and then National Client. Then I need it to move through the 3 sales types for Pipeline Phase - Sold Split.

I can get it to return the data using a helper cell and vlookup, but with that it doesn't return in any sort of order and I end up with a big gap in space between sold and sold split because I have the vlookup formula for sold in the first 50 rows, then sold split in the next 50 rows.

I'm assuming some sort of index/match formula but my knowledge on those is limited.

 

[FDibbins]

1. Please don't "Reply With Quote" unless you need to refer to a specific part of a post - otherwise it's just clutter. Use "Reply" instead

2. We both gave you answers that exactly duplicated the sample answers you showed. You say now you wanted it sorted - based on what?

maybe show some different samples that indicate exactly what you want?

 

[MRobi]

2. We both gave you answers that exactly duplicated the sample answers you showed. You say now you wanted it sorted - based on what?

I still don't see how a SUMIF formula can be substituted for an INDEX/MATCH. I don't see how SUMIF will return the words "Company F" or "New" into a cell on Sheet2. I'm not an excel master, but to my knowledge a SUMIF will only return a number. If it's possible for SUMIF to return text I'm eager to learn how that works.

AhoyNC's method works for bringing the data over to Sheet 2 and is along the realms of how I envisioned it needing to be done. I'd just like it organized now.

As for sorting...

Sorted by:
Sold
Sold Split

Then by:
National
New
Existing

Then by: Dollar amount largest to smallest (not necessary but would be a cool little addon)

I know the sorting can be done with a macro but I prefer to avoid those if possible because I know a large number of these guys will not enable macros and then complain when it's not sorted.

 

[AhoyNC]

Is it possible to sort your data as it's entered on the data sheet. I believe trying to sort with formulas is going to be difficult especially since
National
New
Existing
is not in alphabetical order.

 

[FDibbins]

I still don't see how a SUMIF formula can be substituted for an INDEX/MATCH. I don't see how SUMIF will return the words "Company F" or "New" into a cell on Sheet2

You are correct, it wont, it will only return a numeric answer. My read on your question was that that was the return you wanted, based on what you inputted in terms of criteria.

I have taken another stab at this, and put together a table that pulls ALL unique company names, then I have headings for each Type (new etc), broken down into Phase. See if this will help at all?
Your sample data...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

2​

[/td][td]Pipeline Phase[/td][td]Date[/td][td]Company[/td][td]Sale Type[/td][td]Amount[/td][/tr]

[tr][td]

3​

[/td][td]Prospect[/td][td]9/1/2017[/td][td]Company A[/td][td]National[/td][td]$500 [/td][/tr]

[tr][td]

4​

[/td][td]Quoted[/td][td]9/1/2017[/td][td]Company B[/td][td]New[/td][td]$450 [/td][/tr]

[tr][td]

5​

[/td][td]Sold[/td][td]9/1/2017[/td][td]Company C[/td][td]New[/td][td]$550 [/td][/tr]

[tr][td]

6​

[/td][td]Following Up[/td][td]10/1/2017[/td][td]Company D[/td][td]Existing[/td][td]$700 [/td][/tr]

[tr][td]

7​

[/td][td]Sold Split[/td][td]10/1/2017[/td][td]Company E[/td][td]Existing[/td][td]$1,500 [/td][/tr]

[tr][td]

8​

[/td][td]Sold[/td][td]10/1/2017[/td][td]Company F[/td][td]New[/td][td]$750 [/td][/tr]

[tr][td]

9​

[/td][td]Sold[/td][td]10/1/2017[/td][td]Company G[/td][td]National[/td][td]$600 [/td][/tr]

[tr][td]

10​

[/td][td]Sold[/td][td]11/1/2017[/td][td]Company H[/td][td]Existing[/td][td]$675 [/td][/tr]
[/table]

The extract...
[Table="width:, class:grid"][tr][td] [/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][td]

N​

[/td][td]

O​

[/td][td]

P​

[/td][td]

Q​

[/td][td]

R​

[/td][td]

S​

[/td][td]

T​

[/td][td]

U​

[/td][td]

V​

[/td][/tr]
[tr][td]

1​

[/td][td]

10/1/2017​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td][/td][td]

New​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

Existing​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

national​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

3​

[/td][td]Company[/td][td]Prospect[/td][td]Quoted[/td][td]Following Up[/td][td]Sold[/td][td]Sold Split[/td][td]Prospect[/td][td]Quoted[/td][td]Following Up[/td][td]Sold[/td][td]Sold Split[/td][td]Prospect[/td][td]Quoted[/td][td]Following Up[/td][td]Sold[/td][td]Sold Split[/td][/tr]

[tr][td]

4​

[/td][td]Company A[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]Company B[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]Company C[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]Company D[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

700​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]Company E[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1500​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]Company F[/td][td][/td][td][/td][td][/td][td]

750​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]Company G[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

600​

[/td][td][/td][/tr]

[tr][td]

11​

[/td][td]Company H[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
G1 would be where you enter your date.
G4=IFERROR(INDEX($C$3:$C$10,MATCH(0,INDEX(COUNTIF($G$3:G3,$C$3:$C$10),0,0),0)),"")
copied down
H4=IFERROR(INDEX($E$3:$E$10,MATCH(H$3&$G$1&$G4&$H$2,INDEX($A$3:$A$10&$B$3:$B$10&$C$3:$C$10&$D$3:$D$10,0),0)),"")
copied down, and then across for the 1st Type (new)
M4=IFERROR(INDEX($E$3:$E$10,MATCH(M$3&$G$1&$G4&$M$2,INDEX($A$3:$A$10&$B$3:$B$10&$C$3:$C$10&$D$3:$D$10,0),0)),"")
copied down, and then across for the 2nd Type (Existing)
R4=IFERROR(INDEX($E$3:$E$10,MATCH(R$3&$G$1&$G4&$R$2,INDEX($A$3:$A$10&$B$3:$B$10&$C$3:$C$10&$D$3:$D$10,0),0)),"")
copied down, and then across for the 3rd Type (national)

 

[admiral100]

Hi,

MRobi Did you try Pivot table ?

[TABLE="width: 431"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]Date[/TD]
[TD]01/10/17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Sale Type[/TD]
[TD="align: left"]Company[/TD]
[TD="align: left"]Amount[/TD]
[TD="align: left"]Pipeline Phase[/TD]
[/TR]
[TR]
[TD="align: left"]Existing[/TD]
[TD="align: left"]Company E[/TD]
[TD="align: left"]$1500[/TD]
[TD="align: left"]Sold Split[/TD]
[/TR]
[TR]
[TD="align: left"]National[/TD]
[TD="align: left"]Company G[/TD]
[TD="align: left"]$600[/TD]
[TD="align: left"]Sold[/TD]
[/TR]
[TR]
[TD="align: left"]New[/TD]
[TD="align: left"]Company F[/TD]
[TD="align: left"]$750[/TD]
[TD="align: left"]Sold[/TD]
[/TR]
</tbody>[/TABLE]

 

[MRobi]

@AhoyNC We could arrange Existing, National, New alphabetically if it makes it actually possible to auto sort. At least that way each type would be all together.
@FDibbins That's getting closer, but the ones that don't match sold and the date I don't want showing up at all. So this example Company A, B and C would not be coming up on the export sheet. AhoyNC's formula works perfectly to filter out the ones that don't
@admiral100 The idea of a pivot table did come to mind and would probably be able to do what I'm looking for. But I can't for the life of me get it to format properly (somehow all the data just lays out horizontally over 100's of columns). Also don't they need to be manually refreshed each time? I know they can be set to refresh when the file is opened but I know these guys are going to be looking for real-time as they enter.

My knowledge on pivot tables is very limited.