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)

 

[wideboydixon]

Not quite. Column J was previously being checked for having the value "Live". Is that now a date or is it a different column with the date?

WBD

 

[magpie2000k]

Not quite. Column J was previously being checked for having the value "Live". Is that now a date or is it a different column with the date?

WBD

Well Spotted Not column J but colum T

Also the date in column T is a date time but I would nedd it to match just by the date not time...

Thanks

 

[wideboydixon]

{=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(INT(database.csv!$T$2:$T$6035)=TODAY(),ROW(database.csv!$I$2:$I$6035)-ROW(database.csv!$I$2)+1))),ROWS(C$2:C2))),"")}

Perhaps?

WBD

 

[magpie2000k]

{=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(INT(database.csv!$T$2:$T$6035)=TODAY(),ROW(database.csv!$I$2:$I$6035)-ROW(database.csv!$I$2)+1))),ROWS(C$2:C2))),"")}

Perhaps?

WBD

We I am guessing it is working but it returns blank as the column it is looking in has Day date ie 02/02/2018 13:24:00
And as such Today() returns just 02/02/2018 so no match.

 

[magpie2000k]

Not sure it is working I tested buy putting 02/02/2018 type directly into t2 and it doesnt return it so not working for some other reason.

 

[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

I have tried everything I can think of above but cannot get it to work.

Any help would be greatfully received

Jon

 

[steve the fish]

i just tested the formula in post 14. Works perfectly well. Its an array formula though and will return a blank if not array entered.

 

[magpie2000k]

Argggg where Am I going wrong.

In C3 I have this
=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))),"")
In D3 I have this
=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",ROW(database.csv!$I$2:$I$6035)-ROW(database.csv!$I$2)+1)),ROWS(C$3:C3))),"")
In E3 I have this
=IFERROR(INDEX(database.csv!$T$2:$T$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$3:C3))),"")

And everything works.

When I change C to the updated formula to only pull where date matches it doesnt work. returns a blank cell
=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(INT(database.csv!$T$2:$T$6035)=TODAY(),ROW(database.csv!$I$2:$I$6035)-ROW(database.csv!$I$2)+1))),ROWS(C$2:C2))),"")

Yes all are entered as array
[TABLE="width: 129"]
<tbody>[TR]
[TD="class: xl66, width: 129, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Whats the updated formula? The one in post 14?

 

[steve the fish]

Its going to be an issue with excel not matching dates so paste here an example of the date times in column T of database.csv