I have an "=IF" test for cells in the first column. When the result is "XYZ", I need to fill "XYZ" in a new cell of a second column. Otherwise do nothing. When another one of the cells tests "XYZ", fill the cell on the next row of the second column with "XYZ". How can I do that?
Last edited by dazimon; 05-29-2018 at 02:35 PM.
please attach a sample worksheet and expected result.
To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),
•be sure to desensitize the data
•click “Go Advanced” (next to Post Quick Reply – bottom right),
•scroll down until you see “Manage Attachments”, click that,
•click “Browse”.
•select your file(s)
•click “Upload”
•click “Close window”
•click “Submit Reply”
The file name will appear at the bottom of your reply.
1. Click on the * Add Reputation if you think this helped you
2. Mark your thread as SOLVED when question is resolved
Modytrane
modytrane,
I very much appreciate your suggestion and your precious time devoted to helping me.
Your description can be interpreted in more than one way. Please attach a file with sample data showing what kind of result you want.
Does this mean that the =IF formula appears in the first column, or that the cells it is testing appear in the first column?I have an "=IF" test for cells in the first column.
Do you mean the result of the IF formula is "XYZ"? Or the value of the cell it is checking is "XYZ"?When the result is "XYZ"
What is the value of just having a list of cells that all say "XYZ", the same number of times that "XYZ" appears in the other column?fill the cell on the next row of the second column with "XYZ"
With these assumptions:
- Your IF formula is in column A
- The result of your IF formula can be "XYZ"
- You want your results in column B
- Row 1 contains headers and your data starts in row 2
This formula in the second column will add an "XYZ" in the next cell every time a new XYZ appears in column A. You must extend this formula down for as many rows necessary. Put in B2 and copy down.
Formula:
=IF(COUNTIF(A:A,"XYZ")>=ROW()-1,"XYZ","")
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
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.
Doesn't matter. Solution with my formula attached.Originally Posted by dazimon
OK. Let me throw another monkey wrench into the works. I actually need for the result of the formula to appear in the next available cell in column "K", but I do not know what it will be. That is because it will be applied to several sheets, each one of different value, a result of concatenation in column "J". See the updated Test Sheet.
Is it possible using functions, arrays, VB? Thanks!
Last edited by dazimon; 05-30-2018 at 02:02 PM.
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.
6StringJazzer,
Thank you very much! That'll work.
Now I have your code and can play with it an learn a bit. The real spreadsheet has 14 sheets like the one I shared. It will be fun to see whether I'll manage learn and place results of your code from each sheet into one column on sheet #15 or have to use 14 columns which would be easier. I could use the 14 columns as helping columns to feed the one column with results from all the sheets.
Once again, thank you very much for having spend the time to help me.
Last edited by dazimon; 05-31-2018 at 02:01 PM.
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:
- 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?
- 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?
Here's a low tech offering:
Use what you have so far on the 'New Tally' sheet (with some minor modifications) as a helper column, perhaps move it to column XFD or hide it if you don't want anyone else to know that it is there. Then populate cells A2:A7 using*:Formula:=IFERROR(INDEX(XFD$2:XFD$7,SMALL(IF(XFD$2:XFD$7<>0,ROW($2:$7)-1),ROW(1:1))),"")
*Denotes an array entered formula which needs to be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
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.
Duplicate posting. Content deleted.
Last edited by dazimon; 07-05-2018 at 01:29 PM. Reason: duplicate posting
"low tech is fine with me."
Good to hear because there is more of it in this proposal.
Cells XEV1:XEZ1 are populated with Sheet1, Sheet2 ...
Cells XEV2:XEZ5 are populated using*:Formula:=IFERROR(INDEX(INDIRECT(XEV$1&"!J2:J20"), MATCH(0, IF(ISBLANK(INDIRECT(XEV$1&"!J2:J20")), 1, COUNTIF(XEV$1:XEV1, INDIRECT(XEV$1&"!J2:J20"))), 0)),"")
Cells XFD2:XFD20 are populated using*:Formula:=IFERROR(INDEX(XEV$2:XEZ$5,IF(MOD(ROW(A1),5)=0,5,MOD(ROW(A1),5)),ROUNDUP(ROW(A1)/5,0)),"")
Cells A2:A20 are populated using*:Formula:=IFERROR(INDEX(XFD$2:XFD$20,SMALL(IF(XFD$2:XFD$20<>"",ROW($2:$20)-1),ROW(1:1))),"")
*Denotes array entered formula as discussed in post #15.
Let us know if you have any questions.
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".)
I don't remember any zeroes showing up. When you say "took care of" do you mean something like hiding them with conditional formatting? If so the formula will still pick that up. Other than that I would really need to see a sample of the data that is causing the problem.
Sorry not to be of more help.
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!
I have tried some scenarios but I could not replicate an instance where the formula would display empty rows between rows of proper results. If you are not able to upload a sample that shows the issue, then perhaps selecting one of the empty cells and using the Evaluate Formula feature will give a hint as to the cause.
Thanks. That issues has fallen of the priority list for the time being. :-)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
dazimon 
Register To Reply
Bookmarks