For each cell that tests "XYZ" in one column, add "XYZ" in a new cell of another column

6StringJazzer,

Thanks for you quick response! I realize it is a bit more complicate because the "XYZ" is a result of a function. That's why, as suggested by modytrane, I will upload a test sheet.

modytrane,

I very much appreciate your suggestion and your precious time devoted to helping me.

I don't understand what your latest description means. The essential problem is still the same.

Your file actually has "XY Z" with a space, so my formula needs to be changed. There is also no header, so the "-1" must be removed. Here is the result.

If this is not what you want, then you are going to need to show an example of the result you want.

Thank you for your patience. The results I need are in column "L" now.

That is much clearer. There is a way to do that but I will have to look it up.

By the way, it is quite different than the question you posted. The whole XYZ reference seems to be a red herring. I think your question is more like this:

Create a column that lists all of the results from another column, skipping any blank results.

You are correct. Not being a programmer, I didn't pose the question right. To add to the way you formulated my need, "list only one instance of the result of concatenation for each 'XY Z', 'AB C', etc.", as shown in column "L". Once again, I appreciate your patience and time you're taking to help me.

OK, here is a formula solution that uses an array formula. However, it can't start in row 1. So I created one column with the result starting in row 2, and then another column that references the solution one row down, so it starts in row 1. You can hide the "helper" column if you don't want it to show.

I am assuming that this is exactly how your real file is laid out except for the data.

I did find a little time to try and place results of your code (used on the "Original" sheet of the attached test spreadsheet) from each sheet (1-5) into one column on another sheet ("New Tally").

I encountered two unwanted phenomena:

1. Unlike in your solution in the "Original" sheet, when the code in the "New Tally" column A finds no missing Approved date in column H of the sheet it looks the data up in, the cell in column A on "New Tally" sheet displays "0" or remains empty. It doesn't get filled with the results of the first next line calculation that reflects the fact an Approved date is missing on one of the five sheets. I presume it is because each row refers to different sheet data. Is there a way to overcome this shortcoming?
2. The first instance of a sheet (1-5) that has no missing Approved date, produces "0" in the corresponding cell in column A on "New Tally" sheet. Each additional instance produces an empty cell. Is there a way to make replace the "0" with empty cell?

JeteMc,

low tech is fine with me. I appreciate your putting in the time to make it work. You solved my challenge of tallying data from multiple sheets. Thank you!

Well, I found out that after yet another enhancement to the spreadsheet, there's one more issue. If there are several items in column "J", only the first one surfaces in the "New Tally" sheet.

To demonstrate, I implemented the enhancement on Sheet 1. (The value from "C" is added to the concatenation statement, because there are two types of items, "QA" and "SA". I need each item that still needs approval to show up in "New Tally", because even if they're of the same type, they are specific, unique items.

JeteMc,

Thank you very much. It is working like a charm. :-)

The only glitch in the real spreadsheet that I applied the solution to is that a couple of times the column "A" in the "New Tally" sheet will include not only the proper results, but one or several empty rows. What type of phenomena in would lead to that occurring? (I took care of zeroes showing up, but even after close examination of data and patterns can't figure out what causes the empty cells in the "New Tally" column "A".)

The zeros occurred because I have additional sheets in the real spreadsheet in which I had not yet included the formulas. So you never saw this problem and on my end I've fixed by inserting your formula.

The remaining issue is just the empty rows at certain points of the column "A". Not being a programmer, I just use Excel help, this forum and detecting patterns to figure things on the rare occasion I program a spreadsheet.

I know I'm repeating myself, but given the formula,

=IFERROR(INDEX(XFD$2:XFD$20,SMALL(IF(XFD$2:XFD$20<>"",ROW($2:$20)-1),ROW(1:1))),"")

what condition would make it insert an empty row(s)?

Thank you!

Thanks. That issues has fallen of the priority list for the time being. :-)