Hi I have question.
I have some values at M10 then M26 and M43 …. Etc and I would like to transfer these values to C W10,W12,W13 etc skipping the in-between cells
Could someone provide me with a formula and also explain to me how he derived it?
Come of the in-between cells are not blanc so this is the hard part
Thank you
There appears to be no consistent pattern to your cells, unless one can be found, such a formula is close to, if not impossible.
Please attach a sample workbook, using fictional data if the real version contains anything personal / confidential, but with the data in a format that it accurately represents your real sheet.
Manually enter the expected results into your sample, using different colours / cell comments to highlight these results and any associated criteria.
Hi I have attached a sample document below. All iw ant is to move the yellow cells to the cells marked while skipping the cells, the values are from a machine so they must be a pattern
sample.xlsx
This works with the sample.
Enter this into W8
=MATCH("zzz",A:A)
Then enter this into W10 and confirm it as an array formula by pressing Shift Ctrl and Enter together, not just Enter.
=IFERROR(INDEX(M:M,SMALL(IF(LEFT($A$1:INDEX(A:A,$W$8),5)="Cycle",ROW($A$1:INDEX(A:A,$W$8))+2),ROWS($W$10:$W10))),"")
Then drag the formula down until it returns a blank.
Note that the formula in W8 is to find the last row of data in column A so that the main formula doesn't have to process too much data. This can be hidden away using a named range if preferred.
Ok I see this very complicated for me to understand, but now I realised that I made a mistake copying and pasting data here.
This is more like the data i want to get. The number of the cells between the cells containing the values I want to retrieve is the same.
Thank you for your fast reply and for your time and could you please explain how to derive this formula. Sorry for the previous mess
sample 2.xlsx
Would you be OK using a helper column with this? You csn hide the column and it will make the formula much simpler?
Put this in N3 and copy down...
=IF(AND(A1="cycle: 1",M3>1),N2+1,N2)
then to pull out your answers...
V10=IFERROR(INDEX(M:M,MATCH(ROW(A1),N:N,0)),"")
copied down
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I don't think you made a mistake with copying and pasting, looking at the 2 samples you provided the layout appears to be the same, with the exception of the additional values below some of the yellow cells in the first sample.
No need to apologise, if it was all that easy then there would be no need to have forums to help people, and this type of problem is probably beyond what you would learn on an average course (I can't say for sure there, I've never actually done one).
Starting from the beginning, my observation of the pattern in your samples was that for every value required from column M, the cell 2 rows above that in column A contained "cycle: 1".
Assuming that there is always a possibility of the number at the end increasing, along with the fact that no other cell in column A starts with the word "cycle", I chose to use cells in column A beginning with "cycle" to identify the pattern.
Experience tells me that this problem can only be solved with an array formula (again, something you probably wouldn't learn from an average course). Array formulas tend to be very slow to process with excessive amounts of data, even empty rows (over 1 million of them if you use the whole column) are still processed, even though they are not neededso one of my first thoughts was how to limit this to what is needed.
Given that the number of rows of data in typical use can vary, I used a more complex formula to identify the last row of data in the sheet.
Simplifying the formula very slightly to use a fixed number of rows, the red part below is the critical section. This finds and returns the row numbers for the cells in column A starting with the word "cycle"
=IFERROR(INDEX(M:M,SMALL(IF(LEFT($A$1:$A$70,5)="Cycle",ROW($A$1:$A$70)+2),ROWS($W$10:$W10))),"")
Effectively, if A1 = cycle then ROW will return 1+2, otherwise the formula will return False for that row, same applies for each row down to A70, returning either 70+2 or False.
Although you only see 1 outcome, the array formula holds the information for each row in the background as it calculates, then returns only the relevant result.
Going back to Sample 2, "cycle" appears as the LEFT 5 characters of A8, A26, A44 and A62, so the array returns 8+2, 26+2, 44+2 and 62+2, with all of the other rows returning False.
If you expand outwards in the formula, the results of the last part are passed to the SMALL() function, this function will only see the numbers, all of the False results from the non matching rows are ignored.
=IFERROR(INDEX(M:M,SMALL(IF(LEFT($A$1:$A$70,5)="Cycle",ROW($A$1:$A$70)+2),ROWS($W$10:$W10))),"")
So small only sees 8+2, 26+2, etc. The end part, ROWS($W$10:$W10) counts the number of rows from W10 to W10 (1 row), which tells Small that we are interested in the 1st smallest number (8+2) from the first part of the formula.
** Note that in the 2 parts of the formula, we have used ROW and ROWS, they are completely different, ROW returns a list of the row numbers referred to, ROWS simply counts how many rows there are.
Looking closely at ROWS($W$10:$W10) you see that there is a missing $ between W and 10 in the second part of the range, this is so that when you drag the formula down the column later, the row in the second part increases, so ROWS($W$10:$W10) becomes ROWS($W$10:$W11) giving a count of 2 rows and telling Small that we are now interested in the 2nd smallest number (26+2) from the first part of the formula.
Looking back to the formula, the results from small are passed to INDEX, to return the value from column M, row (8+2) and column M, row (26+2), or M10 and M28 respectively.
As there are only 4 results from matches found for "cycle", asking the formula to return the result for the 5th smallest number will generate an error, so IFERROR is used to blank that out.
Hopefully that all makes sense, I hope I haven't overdone it and lost you anywhere. If you need anything clarified please feel free to ask.
Once you are comfortable with how this works, then we can look at the differences between this and the version I suggested originally.
Last edited by jason.b75; 11-06-2015 at 02:36 PM.
Using the example in your last upload, this ARRAY formula will extract the values from column M.
Enter this formula in column W and fill down until blank cells are returned.
Formula:
Please Login or Register to view this content.
...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. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
[size=3][Table="width:, class:grid"][tr][td] [/td][td]W[/td][/tr]
[tr][td]10[/td][td]879.8333[/td][/tr]
If you want the cell address of the values found enter this in column V next to the values in Column W and fill down.
Formula:Please Login or Register to view this content.
M10 M28 M46 M64
Last edited by newdoverman; 11-05-2015 at 07:41 PM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
I think I got everything the only question I got is at this part LEFT($A$1:$A$70,5) why do you have the ,5. I mean I cant understand where it is used.
I get that ($A$1:$A$70) is an array of column A from 1 to 70 but I dont get the ,5.
Now if I wanted to copy also the values below these cells I assume I would have to change the formula like below, obviously I changed the rows where results should be returned to
=IFERROR(INDEX(M:M,SMALL(IF(LEFT($A$1:$A$70,5)="Cycle",ROW($A$1:$A$70)+3),ROWS($Y$10:$Y10))),"")
and finally Instead of ROWS($Y$10:$Y10) can I just add ROWS($1:1) to allow me to paste the formula in any cell without having to change everytime?
thank you for the responses I will check the answers back tomorrow as it is quite late now here.
Thank you for the answer it is not clear from my sample but in real life the in between cell might have values too and not be empty. Do you have another solution?
I liked this solution too it doesn't have the obligatory cycle term
This is to find the word "Cycle"...which is 5 characters long, in the text in column A. If yoyr text is always Cycle: 1, thenh you can do away with the LEFT() function and just reference the whole wordthe only question I got is at this part LEFT($A$1:$A$70,5) why do you have the ,5
Did you try the suggestion using the helper column?
Do you have examples of what the in between values are in column M and is there any consistency for those values?
Ron, the sample in post #3 is the one I was working from originally, yellow cells in column M are the values to pull with the formula, rest to ignore.Originally Posted by newdoverman
The other method I was looking at was to compare cells in column M on the basis of isnumber(M10)*isblank(M9) but I have a feeling that could get messy, especially when looking at post #9
trofi, will each block of numbers be uniform when compared to the rest of the sheet so that we could look for numbers with empty cells 1 row above?
This might solve the problem of items between the values that you want to retrieve. I have entered a bunch of numbers and alpha characters between the values that you want. It uses OFFSET from Column A whenever Cycle: 1 is found. If column N is blank or any other column that is blank. You can enter this in row 3 and fill down. For this exercise I will use column N.
In N3 enter this formula and fill down. This will extract the value that you are wanting to have from column M and place a copy in column N.
Formula:Please Login or Register to view this content.
The formula for W10 would then be and filled down:
Formula:Please Login or Register to view this content.
Enter with Ctrl + Shift + Enter.
The formula for V10 and filled down:
Formula:Please Login or Register to view this content.
Last edited by newdoverman; 11-05-2015 at 09:25 PM.
trofi,
In both uploads the target values have always been in ascending order. Is this always true of your live data?
If that is the case this formula works without the indexing numbers enabling you to pull the values directly from M:M (similar to newdoverman's formula in post #8). It just identifies the consistent relationship between Cycle: 1 and the target cells and offsets the target range down two rows. It is still array-entered.Formula:Please Login or Register to view this content.
Last edited by FlameRetired; 11-05-2015 at 10:45 PM.
Dave
Hi Everyone I wanted to post a new file that is complete with Data so that I am clear what I am dealing with.
What you see are readings from a machine for some cells treated with compound A in 6 different conditions
V 0.375 0.75 1.25 1.5 2.5 3 5
I have 6 different samples per condition so what I do is average the values I get at C10:J15. So for Condition V I get what it is in cell M10 etc. Below that I calculate
standard error of the mean of my condition ( cell M11).
this is happening for a lot of different time points from time 0 to time 138.7 Hours
What I need to do is to make a table like the one in X7:AF20 with all the average values I get over time together and for all conditions to get a diagram like the one here
I would also like to get a similar table for my standard error values and if possible a small column like the one in X7 where I extract the time points that I insert manually.
I am sorry I didn't post a more complete data set earlier.
The exact position of the two tables is not relevant just next to each other with 1-2 columns space so that I can work with them easily.
I would like to thank everyone that replied for their time and any future contributors.
sample 3..xlsx
I noticed in your data that the values that you were calculating from your raw data were inconsistently placed in relation to to the original data. Most of it started on the same row as Chromatic:1 and a few started on the row with Cycle: 1. I therefore moved the data that started on the row with Chromatic: 1 and moved it down 1 row. This resulted in a consistent data arrangement.
I copied the column letters for the data that was to be extracted to the table and placed them in row 5 just above the two tables that you want created. This allowed a simple formula to be used in each column to extract the values that you want.
Enter this in X8 and fill down to extract the times.
Formula:Please Login or Register to view this content.
Enter this in Y8 and fill across to AF8 and down to extract the averages.
Formula:Please Login or Register to view this content.
Enter this in AH8 and fill across to AO8 and fill down.
Formula:Please Login or Register to view this content.
The yellow row (row 5) can have the font fill colour white to hide the values for esthetic purposes if you like.
Thank you for that Can you please analyse the formula to see what it does I assume
ROWS($M$10:M10)-1 Tell to go extract a ROW in array ($M$10:M10)-1 that means it will extract from Column M row 10 - 1 =9
I font get much from the rest of the formula just bits and pieces.
You were right to correct the data the only reason it was not aligned is because i do that manually so I tend to make small mistakes like that
The statement (ROWS($M$10:M10)-1)*18+10 all goes together. It is a counter that starts in M10 and increments by 18 for each row the formula is filled down and the numbering starts at 10.
Try this in M10 in a worksheet and fill down:
Formula:Please Login or Register to view this content.
You should get:
Ok, could you please explain how you derive the numbers -1, 18 and 10 I want to know in order to be able to replicate it with data in different potitions.
Also what does the X$5 and INDIRECT function do and why did you add a header M,N,P,O above my data?
ROWS($M$10:$M10) counts 1 not 10, because there is only 1 row in the range. ROW($M$10) would return 10. Note ROWS and ROW are different.
Effectively, the section in Ron's formula, (ROWS($M$10:M10)-1)*18+10 returns the same as (1-1)*18+10. This force the multiplier on the counter to start at zero, so you get (0*18)+10, (1*18)+10, etc. as you drag the formula down and the counter increases.
I like Ron's simplified method of extracting your values, especially if it works.
That said, I have a dislike for the use of INDIRECT, if I had thought of this method, I would have used INDEX instead, but from earlier examples, I was unsure that simple methods would work.
=INDEX($M:$M,(ROWS($M$10:M10)-1)*18+9) for the first example.
I don't think that the use of INDIRECT will cause you any issues here, but generally, it is best avoided where possible.
Ok, could you please explain how you derive the numbers -1, 18 and 10 I want to know in order to be able to replicate it with data in different potitions.
Also what does the X$5 and INDIRECT function do and why did you add a header M,N,P,O above my data?
What is the difference with the =INDEX function?
Ok, could you please explain how you derive the numbers -1, 18 and 10 I want to know in order to be able to replicate it with data in different potitions.
Also what does the X$5 and INDIRECT function do and why did you add a header M,N,P,O above my data?
What is the difference with the =INDEX function?
In the statement (ROWS($M$10:M10)-1)*18+10, this is the starting position of the commencement of the incremented values. ROWS($M$10:M10) will return the value 1. The -1 makes the value of the statement to be 0. Multiplying by 18 (the increment value that we want (rows to the next value) still returns 0 at the first cell but the +10 makes the starting value 10. Each row that the formula is filled down will now increase by 18.
The X$5 is referring to the row that I inserted above your data that holds the column letter of the original data (this was for the convenience of writing one formula that could be copied across the columns. The X will change as the formula is copied across and the 5 stays the same. When the formula is filled down, the 5 has to remain constant therefore the $ in front of the 5 which makes the 5 an absolute reference.
The INDIRECT function treats the value produced by AH$5&(ROWS($M$10:M10)-1)*18+11 which is "M11". M11 is the cell reference to the data wanted. The INDIRECT function will return the value from M11.
I added the M N O P Q R S T above your table to eliminate having to write separate a new formula for each column of the two tables.
Without those letters the formulae required would be:
Column X enter in X8 and fill down:
Formula:Please Login or Register to view this content.
Column Y in Y8 and fill down:
Formula:Please Login or Register to view this content.
Column Z in Z8 and fill down:
Formula:Please Login or Register to view this content.
Column AA in AA8 and fill down:
Formula:Please Login or Register to view this content.
etc. This means the difference in writing 3 formulae vs writing 17 formulae.
Here is your workbook without the headers above the data.
As I said above, ROWS returns a count of 1 row, we want the first count to be equal to 0 or the rest of it doesn't work properly. This is the reason for -1 (count of 1 row -1 = 0).
Looking at the data in your sample, if you find the row with the first average formula, the row with the second, they are 18 rows apart.
The first average formula is in row 10.
M,P,N and O.
INDIRECT joins the letter in the cell referred to (M in X$5 for example) and joins it to the number returned by the ROWS calculation to get "M10", "M28", etc. then it returns the value found in that cell.
The difference between INDIRECT and INDEX is that INDIRECT is what is known as a volatile function, INDEX is not.
Volatile functions are recalculated every time something in your sheet is changed, even if the change will have no potential effect on the result of the formula, while non volatile functions, such as INDEX are only recalculated when changes are made that could affect the result.
Used sparingly, it's not a problem, but if you had a few thousand volatile formula in a workbook where you were constantly making changes to data then you would notice the difference.
Sorry, Ron, I have to disagree. It can be done with INDIRECT using 1 formula per table.
In Y8, then fill down and right to AF20
=INDIRECT("R"&(ROWS($M$10:M10)-1)*18+10&"C"&COLUMN(M10),0)
Your msg #23
"What is the difference with the =INDEX function?"
=INDEX is used to identify the data that you want returned by the elements of the function which are
1. Array - the range where the data is located. It could be a column, a row or a range of columns and rows.
2. Row Number - this is usually a calculated value giving the row in which the value to be found is located within the Array.
3. Column Number - where required (if the Array is a range of both rows and columns) is also usually calculated and in conjunction with the Row Number forms a co-ordinate that holds the value wanted.
INDEX works in some ways like VLOOKUP and HLOOKUP but is more flexible.
You asked earlier for a resource for learning this "stuff". Youtube is a great resource. I have followed ExcellsFun, Contextures Inc., mrexcel channel, are a few of the many resources available.
ExcellsFun has both videos and downloadable workbooks so that you can follow along with the lessons.
@jason.b75
Yes, you can do it that way if you like. That makes use of the R1C1 system of cell referencing which I don't think that the OP will understand readily.
Very true, Ron, I know of some seasoned excel users that break out in a cold sweat at the thought of R1C1. I'm quite comfortable with using it for convenience, but the thought of translating $M$10:M10 to R1C1 is making me cringe
I was posting it more as an example that I thought you might have missed.
trofi, this is the INDEX equivalent to my formula in post #26, as Ron has commented already, you will probably have difficulty understanding that one.
=INDEX($M:$T,(ROWS($M$10:M10)-1)*18+10,COLUMNS($M$10:M10))
@jason.b75
I don't break out in a cold sweat when I see it but I used to swear at it a lot. Tracing problems with R1C1, I think, is far too much work!
Using only R1C1 style
=INDIRECT("R"&(ROWS($M$10:M10)-1)*18+10&"C"&COLUMN(M10),0)
becomes
=INDIRECT("R"&(ROWS(R10C13:R[2]C[-12])-1)*18+10&"C"&COLUMN(R[2]C[-12]),0)
No, I didn't figure that out
Everything is relative to the cell in which the formula is written. Who would think that (ROWS(R10C13:R[2]C[-12])-1) would actually return 0 as it does in the above formula? No wonder R1C1 isn't used much! One would go crazy when dealing with a lot of ranges.
Last edited by newdoverman; 11-06-2015 at 05:20 PM.
@Ron
I think sometimes the complexity can vary with the way a person constructs their own formula, when I'm posting something that could be compared to an existing method or earlier post, I try to keep the format similar so that those involved can recognise the changes.
If I was starting the same formula with no base example, I would have gone with using the cell holding the top left formula as the base for the ROWS and COLUMNS functions, i.e. in A1 format,
=INDIRECT("R"&(ROWS($Y$8:Y8)-1)*18+10&"C"&COLUMNS($Y$8:Y8)-1+13,0)
Which in R1C1 would be
=INDIRECT("R"&(ROWS(R8C25:RC)-1)*18+10&"C"&COLUMNS(R8C25:RC)-1+13,0)
Which, as long as you understand R1C1, is easier to work with than R[2]C[-12].
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks