Creating a list of customers from an excel sheet

[magpie2000k]

I have a spread sheet where in:

column a I have the customer name
in column i I have the sale person that sold to that customer.

I am trying to list under the sales persons name on another sheet all the customers they have sold to.

any help would be great ( I wish to avoid VB if poss)

 

[steve the fish]

Id definately use a pivot table to do this. Very simple indeed. A formula is going to be very heavy on the spreadsheet and slow it down.

 

[wideboydixon]

Something like this:

Book1
	A	I
1	Customer	Salesperson
2	customer 1	salesperson 1
3	customer 2	salesperson 2
4	customer 3	salesperson 1
5	customer 4	salesperson 3
6	customer 5	salesperson 4
7	customer 6	salesperson 2
8	customer 7	salesperson 2
9	customer 8	salesperson 3
10	customer 9	salesperson 2
11	customer 10	salesperson 1
Sheet1

Book1
	A	B	C	D
1	salesperson 1	salesperson 2	salesperson 3	salesperson 4
2	customer 1	customer 2	customer 4	customer 5
3	customer 3	customer 6	customer 8
4	customer 10	customer 7
5		customer 9
Sheet2

Cell Formulas
Range	Formula
A2	{=IFERROR(INDEX(Sheet1!$A$2:$A$100,SMALL(IF(Sheet1!$I$2:$I$100=A$1,ROW(Sheet1!$I$2:$I$100)-ROW(Sheet1!$I$2)+1),ROWS(A$2:A2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy formula across and down as necessary.

WBD

 

[magpie2000k]

Hi The pivot table is how we do it at the moment but it is more I want from this than I have explained so I am trying WBD method.

But getting nowhere.

In column a as I said I have cusomer name
In column I I have sales persons name
In column H I have the sales value
and in column P I have the next contact date (ie when we are due to call that customer back.)
Colum G is quotation date (when we sent the quote to the customer)
Column J is quote status (which would need to be in status of "live" to be listed)

I am trying to create a dialy pipeline for each sales person ie a table for each sales person[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Josh (sales person)[/TD]
[TD]value[/TD]
[TD]Call back date time[/TD]
[TD]Days since quoted[/TD]
[/TR]
[TR]
[TD]abc Taxis[/TD]
[TD]£500[/TD]
[TD]31/01/2018 2pm[/TD]
[TD]14 days[/TD]
[/TR]
[TR]
[TD]bishops school[/TD]
[TD]£1500[/TD]
[TD]31/01/2018 10am[/TD]
[TD]31 days[/TD]
[/TR]
[TR]
[TD]brighton hospital[/TD]
[TD]£2290[/TD]
[TD]31/01/2018 3pm[/TD]
[TD]58 days[/TD]
[/TR]
[TR]
[TD]and so on[/TD]
[TD][/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][/TD]
[TD][/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]
</tbody>[/TABLE]
Keep in mind We have 50 sales staff so the list is quite big when looking at all quotes
But I only need to pull the results for 1 team which is 20 staff and ignore all other quotes.

Does that make sense, all the other columns hold data such as email address of customer and phone numbers postcode etc which I dont need to pull.

Maybe I should have explaine more fully before but thought If I could work out the basic I could build on it.

Thanks in advance

Jon

One further thing The report is exported daily from our crm in csv format to our server so I want to look at that csv using a seperate report spread sheet make sense?

 

[magpie2000k]

can it be done if I use VBA maybe

I have tried everythign I can think of my end.

 

[wideboydixon]

Book1
	A	G	H	I	J	P
1	Customer Name	Quotation Date	Value	SalesPerson	Status	Next Contact Date
2	ABC Taxis	17/01/2018	£500	Josh	Live	31/1/2018 2pm
3	Poundland	11/01/2018	£1	Josh	Dead	31/1/2018 12pm
4	Bishops School	31/12/2017	£1,500	Josh	Live	31/1/2018 10am
5	Brighton Hospital	04/12/2017	£2,290	Josh	Live	31/1/2018 3pm
Sheet1

Book1
	A	B	C	D
1	Josh	Value	Call back date time	Days since quoted
2	ABC Taxis	£500	31/1/2018 2pm	14 days
3	Bishops School	£1,500	31/1/2018 10am	31 days
4	Brighton Hospital	£2,290	31/1/2018 3pm	58 days
Sheet2

Cell Formulas
Range	Formula
A2	{=IFERROR(INDEX(Sheet1!$A$2:$A$100,SMALL(IF(Sheet1!$I$2:$I$100=A$1,IF(Sheet1!$J$2:$J$100="Live",ROW(Sheet1!$I$2:$I$100)-ROW(Sheet1!$I$2)+1)),ROWS(A$2:A2))),"")}
B2	{=IFERROR(INDEX(Sheet1!$H$2:$H$100,SMALL(IF(Sheet1!$I$2:$I$100=A$1,IF(Sheet1!$J$2:$J$100="Live",ROW(Sheet1!$I$2:$I$100)-ROW(Sheet1!$I$2)+1)),ROWS(A$2:A2))),"")}
C2	{=IFERROR(INDEX(Sheet1!$P$2:$P$100,SMALL(IF(Sheet1!$I$2:$I$100=A$1,IF(Sheet1!$J$2:$J$100="Live",ROW(Sheet1!$I$2:$I$100)-ROW(Sheet1!$I$2)+1)),ROWS(A$2:A2))),"")}
D2	{=IFERROR(TODAY()-INDEX(Sheet1!$G$2:$G$100,SMALL(IF(Sheet1!$I$2:$I$100=A$1,IF(Sheet1!$J$2:$J$100="Live",ROW(Sheet1!$I$2:$I$100)-ROW(Sheet1!$I$2)+1)),ROWS(A$2:A2)))&" days","")}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[magpie2000k]

unless I am going mad I have made a mistake in the second formula

{=IFERROR(INDEX('Tutorcare_Quotation_31_01_2018(1).csv'!$H$2:$H$6035,SMALL(IF('Tutorcare_Quotation_31_01_2018(1).csv'!$I$2:$I$6035=C$2,IF('Tutorcare_Quotation_31_01_2018(1).csv'!$J$2:$J$6035="Live",ROW('Tutorcare_Quotation_31_01_2018(1).csv'!$I$2:$I$6035)-ROW('Tutorcare_Quotation_31_01_2018(1).csv'!$I$2:$I$6035)+1)),ROWS(C$3:C3))),"")}

C2 being the cell with the name in ie in example Josh.

Basically every quote is returning the value of the first one.

 

[wideboydixon]

{=IFERROR(INDEX('Tutorcare_Quotation_31_01_2018(1).csv'!$H$2:$H$6035,SMALL(IF('Tutorcare_Quotation_31_01_2018(1).csv'!$I$2:$I$6035=C$2,IF('Tutorcare_Quotation_31_01_2018(1).csv'!$J$2:$J$6035="Live",ROW('Tutorcare_Quotation_31_01_2018(1).csv'!$I$2:$I$6035)-ROW('Tutorcare_Quotation_31_01_2018(1).csv'!$I$2)+1)),ROWS(C$3:C3))),"")}

WBD

 

[magpie2000k]

Yeah that worked and could see where I went wrong these are what I have for the next

{=IFERROR(INDEX('Tutorcare_Quotation_31_01_2018(1).csv'!$P$2:$P$6035,SMALL(IF('Tutorcare_Quotation_31_01_2018(1).csv'!$I$2:$I$6035=C$2,IF('Tutorcare_Quotation_31_01_2018(1).csv'!$J$2:$J$6035="Live",ROW('Tutorcare_Quotation_31_01_2018(1).csv'!$I$2:$I$6035)-ROW('Tutorcare_Quotation_31_01_2018(1).csv'!$I$2)+1)),ROWS(C$3:C3))),"")}

The last one I have working fine

Thank you so much will save me hours when built. one last favour can I remove the word days from the last formula so I can sum them.,..

 

[magpie2000k]

OK I need to add an extra condition to the first formula
{=IFERROR(INDEX(database.csv!$B$2:$B$6035,SMALL(IF(database.csv!$I$2:$I$6035=C$2,IF(database.csv!$J$2:$J$6035="Live",ROW(database.csv!$I$2:$I$6035)-ROW(database.csv!$I$2)+1)),ROWS(C$2:C2))),"")}

I want to include only return a value if the date in cells database.csv!$J$2:$J$6035=TODAY

Does that make sense