How do I resolve this, Reference and Logical formulas, but it doesn't work?

[Felix Murillo]

Hello again, this is the challenge (my apologize for my English, I'm still learning):


I have columns of data information ( 45 columns, 4000 or more of rows data), the first column that I need has a lot of customer's account numbers and user names, and I want to retrieve the following:


1. Which user did the first register (the customer appears several times)

2. Which user did the last register (touched the same account, in the same day)

3. It has column time and date (9/23/11 15:38:24)

4. Data are not in order of dates.


¿How can i know which user did the first and last register, knowing this: the information that i need, is in the middle of other same registers?


Example:


Column A Column B

234536A A1 (9/23/11 14:16:34)

234536A A2 (8/12/11 11:16:34)

234536A A3 (10/02/11 14:16:34)

234536A A4 (9/23/11 18:16:34)


I've tried using vlookup with Max/Min or Large/Short, and it's failure.


I'll be really appreciated if you help me with this problem.

 

[pacochin]

Hi Felix,


Possible for you to upload somewhere a sample of the file, so that I can understand whats your situations.

thanks

 

[vumahesh1]

find max and min from the Column B and use index-match to retrive the corrosponding user details from column A

 

[Mike86]

Felix,


I appologize in advance if this isn't what you were looking for. I started what should've been a short note and got carried away. Pitching it seemed like a waste.


If I'm understanding correctly, column A has at least two pieces of information (account number / user ID) that you need to use separately. Column B has date/time information, and may be enclosed in "(" and ")".


Your best bet is to parse, or separate, the account number and user ID into different columns. This is pretty easy. First, copy the dataset into a new worksheet starting in Col B. Insert a blank column between B and C. Highlight Column B. Go to Data ribbon and select "Text to Columns" > delimited > using a space and click "finish". You should now have two columns with the account number and user name. If the time data has "(" and ")" in the cells, use Find/Replace to remove them. Add a Key column to number all the rows. I'd now set up named ranges for Entry Numbers, Account, UserID, and TimeStamp data.


If you really don't want to make separate columns, dynamic named ranges like this will work:


Entries=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)

-- Unique number (1-n) for each row. The -1 includes a header row.


Account=LEFT(INDIRECT("'"&"Sheet1"&"'!b2:b"&(ROWS(Entries)+1)),7)


User=Right(INDIRECT("'"&"Sheet1"&"'!b2:b"&(ROWS(Entries)+1)),2)


Now, you need a list with all the unique account numbers in it. I'd use a separate tab for this. If an account list already exists, great. Otherwise, assuming the accounts are pretty active, you could put together something close from the available data using the Data > Remove Duplicates function.


Bringing everything together is now not all that hard with array and time functions. For example, assuming the unique account numbers are listed on the account list tab in column A starting in Row 2, the array function "=Sum((Account=a2)*1)" will give you the number of transactions in the named range "Account" for the first account number. Evaluating the TimeStamp range using the time and array functions can be done (ex: "Large((Account=a2)*(trunc(TimeStamp)=Date(2011,9,1))*(TimeStamp),1)" will give you the last activity date/time on the account number in cell a2 for 01Sep11).


I added a single cell named range for a specific date called InquireDate. This will be the date of interest.


Here are the array functions I used to do this: (Note Ctrl+Shift+Enter needed for each)


=SUM((Account=A2)*(TRUNC(TimeStamp)=InquireDate)*1)

--in Col B. Gives the number of transactions on Account in A2 on inquire date.


=IF(B2>=1,LARGE((Account=A2)*(TRUNC(TimeStamp)=InquireDate)*TimeStamp,1),"No Activity")

--In Col C. Checks that the account was used at least once. Displays last time stamp or “no activity”.


=IF($B2>=1,INDEX(User,SUM((TimeStamp=C2)*(Entries))),"None")

--In Col D. Displays user name for the last transaction


=IF($B2>=1,LARGE((Account=A2)*(TRUNC(TimeStamp)=InquireDate)*TimeStamp,$B2),"No Activity")

--In Col E. Displays earliest time stamp for account on date or “no activity” message.


=IF($B2>=1,INDEX(User,SUM((TimeStamp=E2)*(Entries))),"None")

--In Col F. Displays earliest user on Account for day.


After the first row is in, you can copy the functions down to cover all the account numbers.


This should work, but I didn't use a large test set.

 

[Felix Murillo]

Hi Pacochin,


Please, guide me how to upload a file, and I'll be more than happy to share the example excel file.


Thanks in advance.

 

[vasim]

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

 

[Anar]

Felix,


Upload it to docs.google.com