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)

 

[magpie2000k]

No Date Set

<colgroup><col style="mso-width-source:userset;mso-width-alt:4425;width:91pt" width="121"> </colgroup><tbody>
[TD="class: xl63, width: 121, align: right"]23/10/2021 09:30[/TD]

[TD="class: xl63, align: right"]24/10/2021 09:30[/TD]

[TD="class: xl63, align: right"]25/10/2021 09:30[/TD]

[TD="class: xl63, align: right"]14/03/2018 17:03[/TD]

[TD="class: xl63, align: right"]11/04/2018 15:31[/TD]

[TD="class: xl63, align: right"]24/08/2018 10:00[/TD]

[TD="class: xl63, align: right"]06/02/2018 13:00[/TD]

[TD="class: xl63, align: right"]08/02/2018 15:16[/TD]

[TD="class: xl63, align: right"]12/02/2018 14:00[/TD]

[TD="class: xl63, align: right"]15/02/2018 14:00[/TD]

[TD="class: xl63, align: right"]31/01/2018 10:00[/TD]

</tbody>

 

[steve the fish]

Ok so theres the answer. You will produce an error should you use INT on 'No date set'. You could use this:

<today()+1,row($i$2:$i$6035)-row($i$2)+1)))),rows(c$2:c2))),"")< html="">{=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",IF(database.csv!$T$2:$T$6035>=TODAY(),IF(database.csv!$T$2:$T$6035 < TODAY()+1,ROW(database.csv!$I$2:$I$6035)-ROW(database.csv!$I$2)+1)))),ROWS(C$2:C2))),"")}</today()+1,row($i$2:$i$6035)-row($i$2)+1)))),rows(c$2:c2))),"")<>

 

[magpie2000k]

Thought it might be that hence selecting that bit of the data with it showing
this being ?

That works but I guess I need to add that extra sector into each formula as now the call back dates and values are incorrect.

BOOM GOT IT

=IFERROR(INDEX(database.csv!$H$2:$H$6035,SMALL(IF(database.csv!$I$2:$I$6035=C$2,IF(database.csv!$J$2:$J$6035="Live",IF(database.csv!$T$2:$T$6035>=TODAY(),IF(database.csv!$T$2:$T$6035 < TODAY()+1,ROW(database.csv!$I$2:$I$6035)-ROW(database.csv!$I$2)+1)))),ROWS(C$3:C3))),"")

that is one of the others and it works...

Yippee

 

[wideboydixon]

Sorry; I've been away for a few days. Glad this got sorted without my input

WBD

 

[magpie2000k]

No Thankyou for putting me on the right lines and thanks to Steve for finishing it off. Have learned a lot

cheers guys.

 

[magpie2000k]

Basically I have this out putting from our CRM the pipeline for each memeber of staff
However pipelines are not even and I wish to display this in a way that doesnt use up wasted space.
IS there a way to show the tables removing the gaps that you guys can think of..

Joshs pipeline for today finishes in row 6 but I have it set to 26 cells down to make sure I get them all so Stuarts starts in row 27

But if Josh has only 4 things in pipeline I would like to display stuarts starting in row 8 Make sense.. Not sure it is possible but thought I would aks..

[TABLE="width: 558"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Josh Emeny3[/TD]
[TD]Value[/TD]
[TD]Call back Date Time[/TD]
[TD]Days since Quoted[/TD]
[/TR]
[TR]
[TD]accac[/TD]
[TD="align: right"]215[/TD]
[TD]05/02/2018 00:00[/TD]
[TD]3 days[/TD]
[/TR]
[TR]
[TD]dsdafsfsd[/TD]
[TD="align: right"]1470[/TD]
[TD]05/02/2018 15:14[/TD]
[TD]11 days[/TD]
[/TR]
[TR]
[TD]sadfasdfasfdsfas[/TD]
[TD="align: right"]875[/TD]
[TD]05/02/2018 14:35[/TD]
[TD]12 days[/TD]
[/TR]
[TR]
[TD]gegegegeg[/TD]
[TD="align: right"]545[/TD]
[TD]05/02/2018 00:00[/TD]
[TD]18 days[/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]
[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]
[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]
[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]
[TR]
[TD]Stuart Cannings[/TD]
[TD]Value[/TD]
[TD]Call back Date Time[/TD]
[TD]Days since Quoted[/TD]
[/TR]
[TR]
[TD]ewqwqewg[/TD]
[TD="align: right"]995[/TD]
[TD]05/02/2018 14:00[/TD]
[TD]14 days[/TD]
[/TR]
[TR]
[TD]qergergweg[/TD]
[TD="align: right"]909[/TD]
[TD]05/02/2018 15:43[/TD]
[TD]14 days[/TD]
[/TR]
[TR]
[TD]egwwgwegweg[/TD]
[TD="align: right"]790[/TD]
[TD]05/02/2018 11:07[/TD]
[TD]24 days[/TD]
[/TR]
[TR]
[TD]ewggewgweg[/TD]
[TD="align: right"]695[/TD]
[TD]05/02/2018 10:00[/TD]
[TD]26 days[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

It's possible but the formula becomes very complex. With each new person, it gets harder and harder because you have to count the number of previous occurrences and make sure you pull the right data back. Faced with a similar problem, I'd be inclined to have a separate sheet for each person as it's the easiest way to write the formulas and also the easiest way to make sure you find the right data quickly.

WBD

 

[magpie2000k]

I fixed that point with VB however I have now a further requirement if you would be so kind to help

I want to add two more columns to my report so that it pulls the product type from Column U in the database export and the number of items from column V. It is for another of our brands hence teh name change of database

The two possible options are Subscriptions or licences in column U and in column V it is just a number

I have in C at present
=IFERROR(INDEX('LCEL Database.csv'!$B$2:$B$6035,SMALL(IF('LCEL Database.csv'!$I$2:$I$6035=C$8,IF('LCEL Database.csv'!$J$2:$J$6035="Live",IF('LCEL Database.csv'!$T$2:$T$6035>=TODAY(),IF('LCEL Database.csv'!$T$2:$T$6035 < TODAY()+1,ROW('LCEL Database.csv'!$I$2:$I$6035)-ROW('LCEL Database.csv'!$I$2)+1)))),ROWS(C$8:C8))),"")

In D at present
=IFERROR(INDEX('LCEL Database.csv'!$H$2:$H$6035,SMALL(IF('LCEL Database.csv'!$I$2:$I$6035=C$8,IF('LCEL Database.csv'!$J$2:$J$6035="Live",IF('LCEL Database.csv'!$T$2:$T$6035>=TODAY(),IF('LCEL Database.csv'!$T$2:$T$6035 < TODAY()+1,ROW('LCEL Database.csv'!$I$2:$I$6035)-ROW('LCEL Database.csv'!$I$2)+1)))),ROWS(C$9:C9))),"")

In E at present
IFERROR(TODAY()-INDEX('LCEL Database.csv'!$G$2:$G$6035,SMALL(IF('LCEL Database.csv'!$I$2:$I$6035=C$8,IF('LCEL Database.csv'!$J$2:$J$6035="Live",IF('LCEL Database.csv'!$T$2:$T$6035>=TODAY(),IF('LCEL Database.csv'!$T$2:$T$6035 < TODAY()+1,ROW('LCEL Database.csv'!$I$2:$I$6035)-ROW('LCEL Database.csv'!$I$2)+1)))),ROWS(C$9:C9)))&" days",""

In F at present
=IFERROR(TODAY()-INDEX('LCEL Database.csv'!$G$2:$G$6035,SMALL(IF('LCEL Database.csv'!$I$2:$I$6035=C$8,IF('LCEL Database.csv'!$J$2:$J$6035="Live",IF('LCEL Database.csv'!$T$2:$T$6035>=TODAY(),IF('LCEL Database.csv'!$T$2:$T$6035 < TODAY()+1,ROW('LCEL Database.csv'!$I$2:$I$6035)-ROW('LCEL Database.csv'!$I$2)+1)))),ROWS(C$9:C9)))&" days","")

I would like Subscriptions and Licences shown in colum G and Number of items in Column H

Lastly if it is at all possible I would like the items grouped so that the resulting list put all Subscriptions at the top and Licences below

Not sure if that is possible.

Thanks again