Matching Debits and Credits in one column with Vendor Criteria from another column

[jhm5139]

Hello Excel Community!

I have been working on trying to develop a function that will be used to search a specific Vendor name, than return ANY MATCHING Debit (negative value) and Credits (Positive Value) to result in a canceling of transaction value. I was fortunate to find this function, from a very helpful source on this forum..

=IF(B2="","",IF(B2<0,-B2&"_"&COUNTIF(B$2:B2:B11,B2),B2&"_"&COUNTIF(B$2:B2:B11,B2)))

Then follow it up by:

=IF(COUNTIF(C:C,C2)=2,"x","")

This equation assumes Vendor in column A, Debit and credits in column B


the function seems to work great for matching most D's to C's, but it is also matching values that are opposite for different vendors (EG; 200 for one vendor and -200 for a totally opposite vendor).

I was wondering if anyone could put something into place to search vendor criteria, THAN corresponding debits and credits..

You all are the best!

Excel Debit _Credit.PNG

[JohnTopley]

Please post a sample file.

to upload file, click "Go Advanced" then scroll down to "Manage Attachments"

[jhm5139]

Please see attached for sample!

[JohnTopley]

See attached.

I added a column called ID number (of each vendor) which is added to string in C

=IF(C2="","",IF(C2<0,-C2&"_"&COUNTIF(C$2:C2,C2)& "_" &A2,C2&"_"&COUNTIF(C$2:C2,C2)& "_" &A2))

This allows matching of vendor TXs

[jhm5139]

See attached.

I added a column called ID number (of each vendor) which is added to string in C

=IF(C2="","",IF(C2<0,-C2&"_"&COUNTIF(C$2:C2,C2)& "_" &A2,C2&"_"&COUNTIF(C$2:C2,C2)& "_" &A2))

This allows matching of vendor TXs

Awesome job!! Thank you. My next question is this - I am working with thousands of rows of data. Is there an easy way to assign a specific number to the varying instances of vendors?

[jhm5139]

Awesome job!! Thank you. My next question is this - I am working with thousands of rows of data. Is there an easy way to assign a specific number to the varying instances of vendors?

I found this: which reads the column and row number than assigns to text. I'm thinking maybe an if statement could tie that to vendor lookup..

=ROW()&COLUMN()&A2

[JohnTopley]

With Cell A1 blank AND data sorted by VENDOR

in A2

=IF(B2<>B1,A1+1,A1)

copy down

[jhm5139]

With Cell A1 blank AND data sorted by VENDOR

in A2

=IF(B2<>B1,A1+1,A1)

copy down

AMAZING!!! Thank you, John! You the man!

[JohnTopley]

You are very welcome.

If your problem has been resolved, could you please mark thread as solved ("Thread Tools" at top of first post).

[jhm5139]

You are very welcome.

If your problem has been resolved, could you please mark thread as solved ("Thread Tools" at top of first post).

With pleasure