I need to create an Excel function that looks at one column then returns a value based on a difference in another column (Picture of example included)

Question

I'm working for a company, and there was an error with sales entries, so tons of invoices got entered into our system twice. They're managed via CSV imports, and I have a huge list of invoice #s, and what their CSV source is. I have them sorted by invoice #, then source. What I need is a third column that indicates when an invoice # appears in more than one source file, and subsequently enter some sort of indicator into the rows for that second source file. I'm not sure if I'm wording this clearly, so here is a screen shot I mocked up to show how finished result should look. This can be using VBA, a formula, it doesn't really make a difference to me. I'm probably over-thinking this, but I've tried a lot of really complex formulas and they haven't panned out.

score 0 · Accepted Answer · 2017-03-24T15:48:22.330

0

Use a COUNTIFS in C2,

=SIGN(COUNTIFS(A$2:A2, "<>"&A2, B$2:B2, B2))
'alternate with the first digit from the CSV in column A.
=IF(COUNTIFS(A$2:A2, "<>"&A2, B$2:B2, B2), LEFT(A2, 1), TEXT(,))

Fill down.

edited Mar 24 '17 at 15:48

answered Mar 24 '17 at 15:41

Works! Thank you! Doesn't add to the count, it puts a "1" by everything that I need marked, but it's good enough. – user2333837 Mar 24 '17 at 16:38

I need to create an Excel function that looks at one column then returns a value based on a difference in another column (Picture of example included)

1 Answers1