View previous topic :: View next topic 
Author 
Message 
fivefootnine Power User
Joined: 09 Apr 2012 Posts: 60

Posted: Mon Apr 30, 2012 3:54 am Post subject: [SOLVED]Auto duplicate numbers that meet certain criterias. 


This is an update, please see last post!
Hi, I need some advice on a formula that will duplicate certain values found in a serie of rows, depending on specific criterias.
I´ll try my best to explain my idea in an understandable way..
I have an empty row (ROW X) that I wanna fill with values.
I also have three other rows (ROW 1,2 and 3) filled with values, these are the values that will be pasted in ROW X, if they meet certain criterias.
For example, I may want ROW X to display all the values found in BOTH 1 and 3 (not in row 2) ,, (in my example below the numbers would be 1 and 23.)
another example, I may want ROW X to display all the values found in ROW 2 but NOT in row 4 and ROW 1, (doesnt matter if theyre in row 3 or not) (in this case the numbers would be 3,34,79 and 84)
[1] 1, 5, 6, 11, 23, 44, 55, 67,
[2] 2, 3, 5, 11, 34, 67, 79, 84
[3] 1, 7, 8, 10, 23, 41, 52, 62,
[4] 2,11,13,14,20,31,45,66,72
I hope you get my point, i just want some advice on how to think when writing formulas for this,, I´m a newbie, so please have that In mind,,,
Last edited by fivefootnine on Sun May 06, 2012 7:05 am; edited 4 times in total 

Back to top 


UmTheMuse General User
Joined: 05 Apr 2012 Posts: 33 Location: United States

Posted: Mon Apr 30, 2012 7:45 am Post subject: 


Does it have to be a formula? Otherwise, you can use the filter tool. Go to Data>Filter>Standard Filter... Put in your criteria, then hit the button that says More Options. Tick on Copy Results to...and let it know where to put the results.
If Standard Filter doesn't cut it, you might be able to get away with the Advanced Filter. Here's a link to somebody on YouTube explaining how to use it: http://www.youtube.com/watch?v=GenfSqcztSc
Edit: I forgot to add somethings. For what you're trying to do, it's probably easier to just make a single column with all of that data from the other columns. Also, both filters have an option for removing duplicates. Good luck with your project! 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Tue May 01, 2012 7:56 am Post subject: 


You could use the MATCH function with the ISNUMBER or ISNA function.
Attached (5f9.ods) uses array formulae or helper row...
http://www.mediafire.com/view/?zp1zcboicimhbir
Ken Johnson _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Last edited by ken johnson on Sun Sep 23, 2012 9:47 pm; edited 1 time in total 

Back to top 


fivefootnine Power User
Joined: 09 Apr 2012 Posts: 60

Posted: Thu May 03, 2012 9:01 am Post subject: 


Thank you guys,.and thanks for the attached .ods filewonderful!!, it really helped!! thank you and have a great day.. 

Back to top 


fivefootnine Power User
Joined: 09 Apr 2012 Posts: 60

Posted: Fri May 04, 2012 1:40 am Post subject: 


The formulas in the document attached by KEN JOHNSON really nailed my problem as described in my earlier post, but after some thinking I came to the conclusion that there´s still some modification that needs to be done to complete my project.
I have a COLUMN (column S) that has 4 cells (S1S4). This 4 cells will be filled with numbers by automatically match the numbers that´s in the rows between Column A to T (20 numbers) and down to approx. 10 rows.
The criterias for this are the same as in my earlier examples (see post above)..
This MIGHT need to be a SINGLE formula (for each Criteria) that would include ALL of the Columns and Rows between A1T1, row 110, pasted into S1S4.
Also it would demand some kind of filtering as there might be MORE numbers matching the criteria than the limitation of only 4 cells. (only one number in each of the 4 cells).
For this filter I´d want a preset priority order. That would mean that certain numbers (IF they meet the criterias), will be chosen above others..
For example
Following 5 numbers is matching the criteria, (Found in row 2 but NOT in row 1 or 4)
1, 17, 24, 27, 34
Only four of these numbers will make it into cells S1S4. And they will be chosen according to a preset priority list, where each number has a specific priorityvalue (highest is 1 and lowest is 50 for example )
number 1 has Priority value 50 (lowest)
numbe 17 has Priority value 1 (highest)
number 24, 27 and 34 has Priority values between 150.
That would mean that number 1 wouldnt make it into one of the 4 cells this time,,
is this possible in Calc?
I apologise for my english,. thank you and have a great day
/FIveFootNine
Last edited by fivefootnine on Fri May 04, 2012 1:43 am; edited 1 time in total 

Back to top 


fivefootnine Power User
Joined: 09 Apr 2012 Posts: 60

Posted: Fri May 04, 2012 1:40 am Post subject: 


The formulas in the document attached by KEN JOHNSON really nailed my problem as described in my earlier post, but after some thinking I came to the conclusion that there´s still some modification that needs to be done to complete my project.
I have a COLUMN (column S) that has 4 cells (S1S4). This 4 cells will be filled with numbers by automatically match the numbers that´s in the rows between Column A to T (20 numbers) approx. 10 rows down.
The criterias for this are the same as in my earlier examples (see post above)..
This MIGHT need to be a SINGLE formula (for each Criteria) that would include ALL of the Columns and Rows between A1T1, row 110, pasted into S1S4.
Also it would demand some kind of filtering as there might be MORE numbers matching the criteria than the limitation of only 4 cells. (only one number in each of the 4 cells).
For this filter I´d want a preset priority order. That would mean that certain numbers (IF they meet the criterias), will be chosen above others..
For example
Following 5 numbers is matching the criteria, (Found in row 2 but NOT in row 1 or 4)
1, 17, 24, 27, 34
Only four of these numbers will make it into cells S1S4. And they will be chosen according to a preset priority list, where each number has a specific priority value (highest is 1 and lowest is 50 for example )
number 1 has Priority value 50 (lowest)
numbe 17 has Priority value 1 (highest)
number 24, 27 and 34 has Priority values between 150.
That would mean that number 1 wouldnt make it into the 4 cells this time,,
is this possible in Calc?
I apologise for my english,. thank you and have a great day
/FIveFootNine 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Fri May 04, 2012 4:07 am Post subject: 


Attachment (Top 4 Values with respect to priority.ods) shows how to use the INDEX, MATCH and SMALL functions to send values to S1:S4 according to their priority value ...
http://www.mediafire.com/view/?9f9t5d8bw04p46l
Ken Johnson _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Last edited by ken johnson on Sun Sep 23, 2012 9:43 pm; edited 1 time in total 

Back to top 


fivefootnine Power User
Joined: 09 Apr 2012 Posts: 60

Posted: Sun May 06, 2012 1:35 am Post subject: instead of smallest 


Hi and thanks for your great formulas,,
What could be uses instead of SMALLEST to sort numbers as explained below.
The first number in the HELPER row (undependent of value), should be sent to first cell in the "RESULT" row, the next number in helper row (undependent of value) should be sent to the second cell in the RESULT row...
I need this to sort the PRIORITY values.
Thanks in advance and have a great day! 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Sun May 06, 2012 4:53 am Post subject: Re: instead of smallest 


fivefootnine wrote:  What could be uses instead of SMALLEST to sort numbers as explained below. 
Not sure I understand.
Perhaps you are wanting to sort from largest to smallest (descending).
If you have numbers in A1:A20, this formula (in B1 filled down to B20) will return the A1:A20 values in descending order...
Code:  =IF(ROW(B1)ROW(B$1)+1>COUNT($A$1:$A$20);"";LARGE($A$1:$A$20;ROW(B1)ROW(B$1)+1))

Ken Johnson _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). 

Back to top 


fivefootnine Power User
Joined: 09 Apr 2012 Posts: 60

Posted: Sun May 06, 2012 6:10 am Post subject: 


Hi,, actually what I wanna do is perhaps quite simple..
I have a row of numbers separated with blank cells
I just want to move the numbered cells to another row, and let them appear in the same order..
[ ] = blank cell (formula in it)
So...
Here´s row 1 below [ ] = blank cell (formula in it)
[ ] [ ] [8] [ ] [4] [11] [ ] [ ] [3 ]
the formula would move the numbers to another row, like this
[8] [4] [11] [3]
I´m starting to get worried that I´m asking too much questions here, but i guess that´s the purpose of forums.,, when not asking questions here, I learn by studying and modifying the formulas I get here,, It´s a learning process...
thanks and have a great day 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Sun May 06, 2012 6:50 am Post subject: 


So you don't want to sort, you just want to remove blanks.
Attachment (Remove Blanks from Horizontal List.ods) shows how to remove blanks using either a helper row or an array formula.
If you use the array formula don't forget to enter with Ctrl+Shift+Enter and when dragging the fill handle to fill the array formula into adjacent columns you must hold down the Ctrl key.
http://www.mediafire.com/view/?nuww3qo33mw4oxd
Ken Johnson _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Last edited by ken johnson on Sun Sep 23, 2012 9:39 pm; edited 1 time in total 

Back to top 


fivefootnine Power User
Joined: 09 Apr 2012 Posts: 60

Posted: Sun May 06, 2012 7:04 am Post subject: 


Thanks again! You´re the greatest!! 

Back to top 




You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum

Powered by phpBB © 2001, 2005 phpBB Group
