Matching dates to cells

[Donna]

Col A COL B Col C
DATES TRANS ADJ
4/06/07 A 1
4/07/09 C
4/06/07 A 1
4/25/09 C
4/03/09 A 2
4/09/02 C
4/25/09 C
4/03/09 A 2
In C I have put what I would want it to be. If column B = "A" then I
would want the dates that match to be numbered in sequence , so as the 1's
above they both have a date of 4/06/07 so anything that had that date would
be 1, then the next one that is "A" is 4/03/09 any of those dates that have
a "A" in column B then all the corresponding dates would be number 2, and
so on. Also the adjustments are not in order as mine are above, one A with a
corresponding date may be in several lines throughout the column . I hope I
have explained what I need. Thanks in advance for looking at this for
me. Donna

 

[JLatham]

Donna, I'm not certain that I understand completely, but I'll give it a try.
With the code below I ended up with this sequence:

Col A COL B Col C

DATES TRANS ADJ
4/06/07 A 1
4/07/09 C 3
4/06/07 A 1
4/25/09 C 4
4/03/09 A 2
4/09/02 C 5
4/25/09 C 4
4/03/09 A 2

To put the code into your workbook, open it up, press [Alt]+[F11] to open
the VB Editor. In the VBE, choose Insert --> Module and then copy the code
below and paste it into the module presented to you. Run it from Tools -->
Macro --> Macros.

You need to set up the transList string to hold the letters you want to give
sequence numbers to in the order you want them to be sought out. The limit
here is that your Trans codes must (without some code changes) each only be a
single letter.

Sub SequenceTransactions()
'make a string with letters of
'the trans codes, in the sequence
'you want them numbered
Const transList = "AC"
Dim transItem As Integer 'loop control
Dim currentTransItem As String
Dim currentDate As Date
Dim transRange As Range ' reference to trans column
Dim anyTransEntry As Range ' one cell in col B
Dim any2ndTransEntry As Range ' for comparison loop
Dim currentSeqNumber As Integer

'create reference to all trans entries in col B
Set transRange = Range("B2:" & _
Range("B" & Rows.Count).End(xlUp).Address)
'work thru the list of trans codes
For transItem = 1 To Len(transList)
currentTransItem = Mid(transList, transItem, 1)
'test the trans entries for match to the
'current trans code
For Each anyTransEntry In transRange
If anyTransEntry = currentTransItem Then
'test if this entry has a sequence number yet
If IsEmpty(anyTransEntry.Offset(0, 1)) Then
'no seq number, the first of this pair
'save the date
currentDate = anyTransEntry.Offset(0, -1)
'get the next sequence number
currentSeqNumber = currentSeqNumber + 1
'give this one the new sequence number
anyTransEntry.Offset(0, 1) = currentSeqNumber
'now go through them all looking for matching
'trans codes and dates
For Each any2ndTransEntry In transRange
If any2ndTransEntry = anyTransEntry _
And any2ndTransEntry.Offset(0, -1) = currentDate Then
any2ndTransEntry.Offset(0, 1) = currentSeqNumber
End If
Next ' end any2ndTransEntry loop
End If
End If
Next ' end anyTransEntry loop
Next ' end transItem loop
Set transRange = Nothing ' housekeeping
End Sub

 

[Teethless mama]

In C2:
=IF(B2="A",SUM(N(FREQUENCY(IF($B$2:B2="A",MATCH($A$2:A2,$A$2:A2,0)),MATCH($A$2:A2,$A$2:A2,0))>0)),"")

ctrl+shift+enter, not just enter
copy down as far as needed

 

[Max]

My take on your scenario, which notes your line:

.. one A with a corresponding date may be in several lines throughout the col

Presuming you have this source data below in A2:B2 down to say B100
04/06/07 A
04/07/09 C
04/03/09 A
04/25/09 C
04/06/07 A
04/09/02 C
04/25/09 A
04/03/09 A
....

In C2
=IF(SUMPRODUCT((B$2:B2="A")*(A$2:A2=A2))>1,0,SUMPRODUCT((B$2:B2="A")*(A$2:A2=A2)))

In D2:
=SUM(C$2:C2)

In E2, normal ENTER
=IF(B2="A",INDEX(D$2$100,MATCH(1,INDEX((A$2:A$100=A2)*(B$2:B$100="A"),),0)),"")
Copy C2:E2 down as far as required. Hide away cols C & D.

This yields the expected results for the "Adj" col:
04/06/07 A 1
04/07/09 C
04/03/09 A 2
04/25/09 C
04/06/07 A 1
04/09/02 C
04/25/09 A 3
04/03/09 A 2

Success? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[Donna]

Hi
I tried it and I got #N/A in column C. Am I doing something wrong, Idid the
control shift enter.
Thanks
Donna

 

[Donna]

Hi,
I did the first part of the equation but it did not put a 1 on every "A". I
went down about 100 lines and it missed 4??? Am I doing something wrong?
Thanks
Donna

 

[Max]

Please try it again. The set of 3 expressions works together. Do a direct
copy from the response n paste into the startcells' formula bar. Do not
re-type. And it should work exactly as advertised, unless there are
inconsistencies in your data involved in the matching. For eg in col B, the
data may have extraneous white spaces which are difficult to detect. You
could try apply TRIM on col B to remove these, eg in F2, copied down:
=TRIM(B2). Then copy col F and paste special as values to overwrite col B.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Donna]

Max,
Oh my gosh, you are my hero. It works perfectly. I do have one question if
you don't mind. I am putting this formula in a macro. Do you know if I can
copy the formulas when I am doing my macro or do I have to type them in for
them to work.
I am lazy, right! Thank you so much, I will be a hero at work but I promise
to give you credit. Donna

 

[Max]

Welcome, glad to hear. I can't help with the follow-on macro query. You could
hang around awhile for vba savvy responders to jump in here, or start a new
thread in .programming.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik