Hi

I am trying to create a macro for a report that gets
generated regularly from an asset tracking program. It
returns a list with a column for UIC code, a column for
location issued from and one for location issued to plus
a date issued column. So you might have one item issued
out from A to B, then the same item returned from B to A
on a different line. Here's the dilemma, for accounting
purposes I need to track how many days each item was out;
not sure how to do this when it would have to refer to 2
different lines and the second problem is that some items
only went out and didn't come back and so if I sort by
uic, I get two lines for most items but one line for a
few, it also has to track those items that only went out
and didn't come back . Not sure if this is possible but
would sure appreciate any assistance.

sort your data by UIC so the the
Date out is followed by the
Date in lines.

assume the first entry is in row 2 and the date for the row is in column D,
UIC code in Column A

In column E for example, put in a formula like this

=if(A2=A3,D3-D2,if(A2<A1,"Not Returned",""))

then drag fill this down the column.

Adjust D3-D2 to reflect the number of days - if the start date is counted,
D3-D2+1 for example.

you can then apply an autofilter (Data=Filter=Autofilter) and in column E
select the dropdown, select custom and set the conditions does not equal
"Not Returned" and does not equal "". This will just show the returned
items (row showing when they went out) with the number of days they were
out. You can select the data and copy and paste (edit=Paste Special =
Values) to another sheet. Only the visible rows will be copied.

--
Regards,
Tom Ogilvy


"Lorraine" wrote in message
...
Hi

I am trying to create a macro for a report that gets
generated regularly from an asset tracking program. It
returns a list with a column for UIC code, a column for
location issued from and one for location issued to plus
a date issued column. So you might have one item issued
out from A to B, then the same item returned from B to A
on a different line. Here's the dilemma, for accounting
purposes I need to track how many days each item was out;
not sure how to do this when it would have to refer to 2
different lines and the second problem is that some items
only went out and didn't come back and so if I sort by
uic, I get two lines for most items but one line for a
few, it also has to track those items that only went out
and didn't come back . Not sure if this is possible but
would sure appreciate any assistance.

I set it up this way with the titles in row 4 and the
following columns:

A B C D E

uic from to out days out
121 ca ma 1-Jan
131 nv ca 5-Jan
121 ma ca 15-Jan 14
131 ca ma 31-Jan 26
111 nv ma 15-Feb still out
141

I put the following in cell E6 and copied down.

=IF(ISNA(VLOOKUP(A6,$A$5:$D5,1,FALSE)),IF(ISNA(VLO OKUP
(A6,A7:$A$10,1)),"still out"," "),D6-VLOOKUP
(A6,$A$5:$D5,4,FALSE))

I put this in cell E5

=IF(ISNA(VLOOKUP(A5,A6:$A$10,1, FALSE)),"still out"," ")

Is this what you were looking for?

John



-----Original Message-----
Hi

I am trying to create a macro for a report that gets
generated regularly from an asset tracking program. It
returns a list with a column for UIC code, a column for
location issued from and one for location issued to plus
a date issued column. So you might have one item issued
out from A to B, then the same item returned from B to A
on a different line. Here's the dilemma, for accounting
purposes I need to track how many days each item was out;
not sure how to do this when it would have to refer to 2
different lines and the second problem is that some items
only went out and didn't come back and so if I sort by
uic, I get two lines for most items but one line for a
few, it also has to track those items that only went out
and didn't come back . Not sure if this is possible but
would sure appreciate any assistance.



.