I have a sheet with an unsorted, variable length data array. Column A repeats a set of numbers in random order, each of which has a single value in the adjacent columns. Unfortunately, I have no idea how to sort the randomly arranged values.
In the attached Sample file, Sheet 1 is an example of the general layout. Sheet 2 is what I want the data to look like.
Any suggestions would be appreciated!
Last edited by dvess11; 12-30-2018 at 06:05 PM. Reason: attach file
Please try at Sheet2
A2, this only work with number, for Text formula will be longer.
Formula:
Please Login or Register to view this content.
B2:K2
=IF(A2="","",SUMIFS(Sheet1!B:B,Sheet1!$A:$A,$A2))
Copy all A2:K2 down
Based on the assumption that your sample file is an accurate representation of your real data in that all data is numeric and that each column will only have 1 record per entry number in column A.
In sheet2
A1 and drag right until blank
=IF(Sheet1!A1="","",Sheet1!A1)
A2 and drag down until blank
=IF(ROWS(A$2:A2)>MAX(Sheet1!$A$2:$A$41),"",ROWS(A$2:A2))
B2 drag right and down to fill the table.
=IF(OR($A2="",B$1=""),"",SUMIF(Sheet1!$A$2:$A$41,$A2,Sheet1!B$2:B$41))
Thank you Bo. I have no idea why but it worked beautifully!
Jason, I tried your solution and it too worked beautifully. You used a different method than Bo and both of yours produced the desired results. Thank you!
or a rather shorter (!!) array formula in A2:
=IFERROR(SMALL(IF(FREQUENCY(Sheet1!$A$2:$A$41,Sheet1!$A$2:$A$41),Sheet1!$A$2:$A$41),ROWS($2:2)),"")
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
dvess11, better go with Glenn's formula.
Or Glenn's formula with normal Enter
=IFERROR(AGGREGATE(15,6,Sheet1!$A$2:$A$41/(FREQUENCY(Sheet1!$A$2:$A$41,Sheet1!$A$2:$A$41)>0),ROWS(A$2:A2)),"")
My formula in Post#2 is for use with text but shorten down a bit to work with number.
The only reason I can see for the over-engineered formulae suggested for use in A2 would be to allow for the 'Entries' not being consecutive integers, in which case
Array confirmed
=IFERROR(SMALL(Sheet1!$A$2:$A$41,1+SUM(COUNTIF(Sheet1!$A$2:$A$41,$A$1:$A1))),"")
Or an alternative version which many would claim is a regualr formula, not an array
=IFERROR(SMALL(Sheet1!$A$2:$A$41,1+SUM(INDEX(COUNTIF(Sheet1!$A$2:$A$41,$A$1:$A1),))),"")
This still creates the same arrays as the first version, so in my opinion, is an array formula, but the use of INDEX circumvents the need for CSE confirming the array.
You guys all have great skills. Thanks again for the help. One last question: will any of these formulas allow a Column A figure to be "1A" or the like?
Try this one in A2, Array confirmed with Ctrl Shift Enter, then filled down
=IFERROR(INDEX(Sheet1!$A$2:$A$41,MATCH(LARGE(COUNTIF(Sheet1!$A$2:$A$41,">="&Sheet1!$A$2:$A$41),1+SUM(COUNTIF(Sheet1!$A$2:$A$41,$A$1:$A1))),COUNTIF(Sheet1!$A$2:$A$41,">="&Sheet1!$A$2:$A$41),0)),"")
I'm sure there must be a simpler method.
Please try at A2
=IFERROR(INDEX(Sheet1!A$2:A$99,MATCH(0,INDEX(COUNTIF(Sheet1!A$2:A$99,"<"&Sheet1!A$2:A$99)-SUMPRODUCT(COUNTIF(A$1:A8,Sheet1!A$2:A$99)),),)),"")
Last edited by Bo_Ry; 01-01-2019 at 12:06 AM.
Same as Bo's in column A.
Then in B2:K9Formula:Please Login or Register to view this content.
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks