[SOLVED]Auto duplicate numbers that meet certain criterias.

[fivefootnine]

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,,,

[UmTheMuse]

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]

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).

[fivefootnine]

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

[fivefootnine]

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

[fivefootnine]

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]

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).

[fivefootnine]

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]

[fivefootnine]

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]

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).

[fivefootnine]

Thanks again! You´re the greatest!!