OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED]Auto duplicate numbers that meet certain criterias.

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

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

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
View user's profile Send private message
UmTheMuse
General User
General User


Joined: 05 Apr 2012
Posts: 33
Location: United States

PostPosted: Mon Apr 30, 2012 7:45 am    Post subject: Reply with quote

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
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Tue May 01, 2012 7:56 am    Post subject: Reply with quote

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
View user's profile Send private message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Thu May 03, 2012 9:01 am    Post subject: Reply with quote

Thank you guys,.and thanks for the attached .ods filewonderful!!, it really helped!! thank you and have a great day..
Back to top
View user's profile Send private message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Fri May 04, 2012 1:40 am    Post subject: Reply with quote

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
/FIveFootNine


Last edited by fivefootnine on Fri May 04, 2012 1:43 am; edited 1 time in total
Back to top
View user's profile Send private message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Fri May 04, 2012 1:40 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Fri May 04, 2012 4:07 am    Post subject: Reply with quote

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
View user's profile Send private message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Sun May 06, 2012 1:35 am    Post subject: instead of smallest Reply with quote

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
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sun May 06, 2012 4:53 am    Post subject: Re: instead of smallest Reply with quote

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
View user's profile Send private message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Sun May 06, 2012 6:10 am    Post subject: Reply with quote

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
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sun May 06, 2012 6:50 am    Post subject: Reply with quote

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
View user's profile Send private message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Sun May 06, 2012 7:04 am    Post subject: Reply with quote

Thanks again! You´re the greatest!!
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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