Help with excel - Possible IF function?

Something like this then? I can do it without the helper columns as well but that requires VBA.

Formula:

Please Login or Register  to view this content.

This is an array formula, it has to be entered with Ctrl + Shift + Enter.

I found some old code that I modified and recycled, so here is the UDF version.

Please Login or Register  to view this content.

Edit: Small error http://www.excelforum.com/excel-form...ml#post4184061

VBA is a sort of programming language (macro language) that is included in Excel. It can be made to run in the background triggered by buttons or events.
It is also possible to use VBA to write your own custom functions that works just like any other function that you put in a cell. This is called a UDF (User Defined Function).

Oh wow it's actually done! Thank you so much Jacc.

I must be honest though; this is way beyond what I can comprehend on Excel. (VlookUP and Pivot tables are as advanced as I go) and I had no idea my problem needed coding, not once. Thank you for the short introduction on excel coding.

At work, My manager and I are on a short project.

I believe I made the mistake of making such a poor 'Clicks' sheet. If fact, the Clicks sheet did not exist at first.

This is the original problem (I hope its not too exhausting). We have a Master workbook, that contains 20,000 emails addresses in Column A (A1:A20000). We also have 20 non-master excel files that contain the same 20,000 email addresses but between them. For example, non-master Workbook 1 has maybe 5,000 emails through Column A (A1:A5000), non-master Workbook 2 has maybe 2,000 emails through Column A (A1:A2000) etc. It all adds to 20,000 emails in the end between the 20 non-master excel workbooks.

These "IDs" I mentioned are actually unique file names given to every non-master excel workbook. For example, non-master Workbook 1 may have a file name that looks like this: 000001 (not true, just an example). These file-names are very important to us.

I can’t go in-depth as to why we are doing this but in the Master workbook we have 20,000 rows of emails in column A and we just wanted to write in the “ID” next to each email so we know which non-master file contained that unique email address. For example, if [email protected] is in the excel file called ‘000001’ then ‘000001’ will be in B1 next to '[email protected]' which will already be in A1 (of the Master workbook that is).

Also, these 20 non-master files can't be altered, this includes their file name.

We can complete this project if we drink enough coffee and miss xmas, but we thought there would be a faster way and also a way we could comprehend.

So genius me thought to create a new sheet called Clicks, paste each of the 20 file names in row 1 of every column A-T and then paste each data from each non-master file below it’s filename. And from there just try our luck.

I will try to brainstorm and find a better first approach but I truly thank you for trying and achieving what we have tried to complete for the past day.

Another way formula wise. If you have 4 columns / IDs in the clicks sheet this array-entered (Ctrl + Shift + Enter) formula in C2 of Master filled down works at my end.

Formula:

Please Login or Register  to view this content.

If you have more than 4 columns you can expand this, but it will become cumbersome beyond 6 or so.

Does this help?

I don't think you can get around the problem by changing the layout of the data. UDF's may be a little scary in the beginning but maybe this article will help: http://www.cpearson.com/excel/writin...ionsinvba.aspx
It's a matter of having my file and your file open at the same time and then drag and drop the module containing the code over to your workbook. Hit Alt + F11 to get to the VBA editor.
Once the code moduled is copied the UDF function is available, just type LookupConcatX4 into a cell.

If you don't mind putting together a rather long formula then FlameRetired's solution should work just fine.

I found a small error; where it says xlByRows it should say xlByColumns. The difference is that with xlByColumns the headers are listed in the correct order.