[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

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
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!
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
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..
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 (S1-S4). 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 A1-T1, row 1-10, pasted into S1-S4. 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 pre-set 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 S1-S4. And they will be chosen according to a pre-set 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 1-50. 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 /FIveFootNineLast edited by fivefootnine on Fri May 04, 2012 1:43 am; edited 1 time in total
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 (S1-S4). 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 A1-T1, row 1-10, pasted into S1-S4. 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 pre-set 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 S1-S4. And they will be chosen according to a pre-set 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 1-50. 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
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
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!
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
_________________
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
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
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!!
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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