Sigh....excel help needed

[Chessic Sense]

I have a master list of numbers in column A. I have 3 mini lists in columns C, D, and E. I need cell B1 to tell me which list A1 is in, and B2 to tell me which list A2, etc. In other words, I need to write a function that says "B cell, if A cell is found in column C, return the value C. If it's in D, return the value D. If it's in E, return the value E."

It needs to be a function so that it's dynamic. Yesterday, I did a VLOOKUP but I had to do one list at a time and then CONCATENATE them into one list. If I add or subtract from lists C, D, or E, I have to start over. I want the function to do it itself.

[BubbaDog]

Assuming that the list starts in A1

Formula for B1 =

=CHOOSE(MATCH(A1,C1:E1),"C","D","E")

Copy B1 down

I hope I understood you correctly

[Boyo Jim]

The value can be in one or all of the lists B C and D? So you may end up with a B cell with a value of "CDE"?

Is there an option to use additional columns. like a vlookup column for each of CDE columns, and a 4th column that concatenates the results of the first 3?

[BubbaDog]

=CHOOSE(MATCH(A1,C1:E1,0),"C","D","E")

Sorry about that -

You need to add a zero inside the "match" paren to force it to an "exact" matching process. Otherwise you get some ascending errors

[redtail23]

I'm reading your explanation that you have a list in A (let's say A1:A5).

For each value in A, you need to match the value in one of the other columns. So A1.value could be in C1:C5, D1:D5, or E1:E5.

The value will only be in one of the columns; you need to know which one.

Let me introduce you to the magical world of "IF".

IF(ISERROR(VLOOKUP(A1,$C$1:$C$5,1,FALSE)),IF(ISERROR(VLOOKUP(A1,$D$1:$D$5,1,FALSE)),IF(ISERROR(VLOOK UP(A1,$E$1:$E$5,1,FALSE)),"NoMatch","colE"),"colD"),"colC")

This does your vlookup on A1 in column C. If there is no error (VLOOKUP doesn't return #NA), then your answer is "colC".

If VLOOKUP returns an error, then it repeats the process for the next column. If it doesn't find a column after going through all three, it returns "NoMatch".

Is that what you're looking for?


ETA: One thing I would note, this setup will only find the *first* occurence of your value, if it's in more than one column.

[BubbaDog]

redtail23 After reading your message I see that I misunderstood Chessic Sense's problem

[redtail23]

redtail23 After reading your message I see that I misunderstood Chessic Sense's problemI'm not entirely sure that you did. It may have been my misreading. :)

At any rate, that CHOOSE(MATCH) combo is great; I've never used either of those, but I can see them coming in very handy now that I know they exist!

[Chessic Sense]

For clarification, let's just say it's a baseball team roster. Each person is either a pitcher, fielder, or manager. I have 15,000 men on this team. 1,000 are managers, and they're in column C. 7,000 are column D fielders and 7000 are column E pitchers. Each jersey number is used once an only once. Each player is in only one position on the team. So I might have something like:

A b c d
b i A e
c h j
d f g
e
f
g
h
i
j

As you can also see, they're not sorted in the C, D, and E columns and they have to stay that way.

[Chessic Sense]

=CHOOSE(MATCH(A1,C1:E1,0),"C","D","E")

Sorry about that -

You need to add a zero inside the "match" paren to force it to an "exact" matching process. Otherwise you get some ascending errors

The problem with that is that A1 needn't be in C1-E1. It could (and is) in C875 or E 6741. And Choose's position only goes up to 29.

[Kimmy_Gibbler]

Your problem is that this database has been designed in a pretty daft way. Fortunately, there is a simple solution.

Reproduce serially in a single list the entries in columns C, D, and E. That is, in a new sheet, copy the nC entries of column C to cells A1 through AnC, then copy the nD entries of column D to cells AnC+1 through AnC+nD, and finally the copy the nE entries of column E to cells AnC+nD+1 through AnC+nD+nE. As you copy each column into this new sheet, in column B, code for the source column (i.e., enter "C", "D", or "E", according to the column the data were originally in).

Now you can run a VLOOKUP and get the pertinent column information.

If you are paranoid about preserving the order of the original columns, you can code their original position in the new spreadsheet as well. After you copy the list and code the original column letter, in column C, enter a formula that will label each successive cell one number greater. Then, as you finish copying over each individual column, copy the entire selection and use one of the most important tools in Excel: the paste values function. Now the column position data is hard-coded, rather than the result of a formula.

[redtail23]

OK, I follow your example. Have you tried my solution? It should work.

If you're not sure what your ranges are in the field-position columns, or if those lists may change, then you'd probably want to change the range you use for your table array in VLOOKUP to include the entire column (e.g., $C$1:$C$65536). It will take a bit longer to run that way, but should do what you need.

[redtail23]

After you copy the list and code the original column letter, in column C, enter a formula that will label each successive cell one number greater. Then, as you finish copying over each individual column, copy the entire selection and use one of the most important tools in Excel: the paste values function. Now the column position data is hard-coded, rather than the result of a formula. Or you could just use the Fill-Series function, or the fill handle to do the same thing much more simply.

[BubbaDog]

I'm not entirely sure that you did. It may have been my misreading. :)

At any rate, that CHOOSE(MATCH) combo is great; I've never used either of those, but I can see them coming in very handy now that I know they exist!

I tested your method with match and isnumber and got similar results. I don't think there's any advantage/disadvantage to either formula. This formula copied down the B1 column provides the same results. This too only picks up the first occurrence traveling left to right lists.

=IF(ISNUMBER(MATCH(A1,$C$1:$C$100,0)),"List - C",(IF(ISNUMBER(MATCH(A1,$D$1:$D$100,0)),"List - D",(IF(ISNUMBER(MATCH(A1,$E$1:$E$100,0)),"List - E","No Match")))))

I did this for three lists that went to 100 but it can easily be expanded to any allowable length.

I don't much like writing these compound ifs. I always find myself having to tidy up the paren count in there somewhere.

[redtail23]

BubbaDog, I concur. It looks like your formula works as well as mine.

Yeah, the nested IFs. I'm not that fond of them, but somehow I seem to end up using 'em a lot, with lots of other formulas inside. I usually start from the inside and work out to the ifs.

Then I only have to fix the parens one or two places. ;)

(Actually, given my druthers, I'll usually dump it over into Access, where it's a lot easier to do most complicated stuff.)

[Chessic Sense]

RedTail wins. The formula works for what I needed. Unfortunately, I couldn't get it to just return a label. Instead I had to have it return the column header. Whatever. BubbaDog, yours looks cleaner and fits on one line, so you win honorable mention.

Thanks for introducing me to ISERROR and ISNUMBER too. Logged for future use.

As for the design being daft...well, that's the gov't for you. The master list came from a list of 15000 files in one folder that we had to do some filename surgery on. The other lists come from a completely different system. Trust me, there were some major obstacles to overcome. I had to reach back to the DOS prompt for one step of the process. The reason I can't just combine the lists like suggested is because they're incomplete as of yet. We're done with Column C, moving through Column D, and E is blank right now. That's why I needed a dynamic solution. If there is a number that comes up that's not on the master list, or vice versa, I need a way to tell. This will let me do that easily.


Thanks. Now you can all say you've contributed to national security. If you're wise, at the next cocktail party, you can dress it up with something like "The government called me and said they needed my expertise on a computer system. Only I could help." ;)

[Kimmy_Gibbler]

As for the design being daft...well, that's the gov't for you.

Is it? It sounds like you designed it, or at least you are perpetuating poor design choices. Until you apply the principles of mathematical database design, you're just going to be jury-rigging increasingly complex solutions until it becomes unmanageable.* An ounce of prevention is worth a pound of cure, my friend, especially when it comes to complex, ten-thousand-entry databases.

* This is the difference between an IT professional and a mere Excel technician.

[Chessic Sense]

Well if you've got a better idea, throw it out there. Your previous idea was good but the problem is we can't just use the CDE lists. The goal of this is to take the master list of serial (not literally) numbers find out what group each one belongs to (C, D, or E) and most importantly, find out if any of them aren't in the reference lists at all. Remember that the lists are changing daily.

[Kimmy_Gibbler]

Well if you've got a better idea, throw it out there. Your previous idea was good but the problem is we can't just use the CDE lists. The goal of this is to take the master list of serial (not literally) numbers find out what group each one belongs to (C, D, or E) and most importantly, find out if any of them aren't in the reference lists at all. Remember that the lists are changing daily.

How are your C, D, and E columns generated then? Are the entries formulas or values?

[Chessic Sense]

No, they're basically copy/paste but it's a slow process to get all the numbers. The biggest obstacle is that they start on a different system than they finish on. It takes days to get them crossed over.

[Polerius]

As for the design being daft...well, that's the gov't for you. The master list came from a list of 15000 files in one folder that we had to do some filename surgery on. The other lists come from a completely different system. Trust me, there were some major obstacles to overcome. I had to reach back to the DOS prompt for one step of the process. The reason I can't just combine the lists like suggested is because they're incomplete as of yet. We're done with Column C, moving through Column D, and E is blank right now. That's why I needed a dynamic solution. If there is a number that comes up that's not on the master list, or vice versa, I need a way to tell. This will let me do that easily.
Chessic Sense, out of curiosity, do the lists have to be in excel?
Can they, for example, be in text files with one entry per line?

[Kimmy_Gibbler]

Why do the lists change daily? It sounds like you're producing a file directory. List A is the master list, entries on which can be found at one of three locations corresponding to your mini-lists in CDE. You'd also like to know if any of the entries on A are completely missing from CDE.

I can see why these would change if you have finished compiling CDE, in which you should just wait until CDE are finalized. Or the process is more subtle than all that. What is it that you want your Excel database to do, and what raw materials do you have to do that with?

[redtail23]

Chessic Sense, what order are your sub-lists sorted in (e.g, by jersey number or by name or something else)? You said they had to be maintained in that order. Are new ones just added at the end, or might a new entry be stuck somewhere in the middle?

I'm also curious if Excel is a requirement, or if it's just what you're familiar with. There are some other software options that might be easier for this type of project.

Is this a one-time project that's dragging on due to the incoming data issues, or an ongoing new system? If it's going to be maintained indefinitely, then Kimmy_Gibbler has some valid (if poorly expressed) points, and it would probably be worth the time to look at other software possibilities and design.

If it's just a one-time deal, then it's probably not worth the investment. As long as you're getting accurate results, fastest and easiest is the way to go.

[Chessic Sense]

We have a file tied to a serial number. Each day, we open up some (however many we can get through) and do some work with it. Then we check it off the list. We want to track it all in SharePoint. These files exist in two places- attached to a record in a separate system's Sharepoint (which is old, and modified poorly for our purposes) and in a windows folder. Our end state is twofold: We want a master list that's easily searchable of all the ones we've worked on and that we will work on eventually (so the other teams don't touch them and duplicate our work). We also want them all in Sharepoint so we can have other data points besides the serial number sortable, filterable, etc.

The first step has been solved. I just made a directory list of the files in the windows folder. Now we have a master list. This list may or may not match the low-side's Sharepoint records. Hopefully, it does.

C is already in sharepoint. One problem is that we don't have all of D and we don't know any of E, unless we did it manually through all 7000 of them, which we'd done so far for C and half of D til I saved the day and automated it. Go me. The low side knows what the lists are, but we can't tell the high side what they are since they don't communicate with each other.

The original task was "Hey, now that we have this master list, can you figure out which ones are C, D, and E, so that we can automatically import E?" It matters that they be kept separate, you see, so we know what to work on next. My idea was to build this program so that as D comes in, we can subtract that off of the master list so that it's in E. That can then be proofed later on.


Does that makes sense? Any questions?

[Chessic Sense]

Excel is not a requirement, and it's mostly a one-time deal. That is, we have most of the data and we'll be working on it for the next 10 months-ish. A new guy would be added to the bottom of the list, but there really shouldn't be new guys- pitchers can become managers though, for instance. CDE are sorted by date-time stamp.

[Kimmy_Gibbler]

So why do you say the lists change daily? It sounds like a given file's membership in C, D, or E is a fixed property. Moreover it sounds like you have a full list for each column and a list A which should be the union of C, D, and E. Is this correct?

[Chessic Sense]

A given file's membership is mostly fixed. List A is the union CDE, but we don't know what D and E are. We know half of D and we learn more every day. E is unknown unless we go through a painfully slow, manual process of looking them up and crossing them over. That'd take roughly 350 man-hours. What we want to do is maintain a list A that represents what we have and lists CDE that represent what we should have. In theory, those lists are the same but there's maybe 100 or so that aren't in A. But we'll deal with that later. It'll show itself over time anyway.

Right now, we want to use A to figure out what should be in E. I wanted a function that would mark off A as more of D came in. Once we get all of D, we can assume that whatever is left in A belongs in E. We'll then be able to make the 7000 sharepoint records automatically instead of manually, as the team had done for 3 or 4 months before I fixed it.

I think we've got what we need. My boss appreciated it. I said to a coworker that I haven't gone through many records today. He said "Well you've been Excel-ing all day". My boss chimed in "He's been excelling all week." Pun intended. And I have you guys to thank for it.

[redtail23]

Yeah, sounds like what you've got is working well for the situation you've got to deal with. Sometimes the "best" solution, isn't.

Don't forget to be sure your file is recalcing all the formulas when you add new data. (I think it defaults to that, but if you've got it turned off then your setup won't work.)

Good luck and glad we could help!

I'll note my work for National Security so I can use it on a resume. Of course, I won't be able to tell them what it was, or I'd have to kill them. :p