Index/Match - Generating a reference results list from conflated cell values

Hello guys,

Here is the .xls file.

Essentially, I'd like to create a formula across each column (such as column B in my example) that looks at the cell values, calculates whether the *second value* after the hyphen is *lower* than the value before the hyphen, then takes column A's value equivalent and prints all the values into an array in a cell below the column.

For example:

A	B
1	2-2
2	1-3
3	4-1
4	3-4
5	1-2
Result	2,4,5

Hope that helps, sorry if my title was confusing.

Cheers

Originally Posted by FDibbins

Hi, welcome to the forum

1st, it is almost always better if you upload a sample workbook to the forum, not all members are willing - or able - to access google docs
2nd, if you have data (values) in a cell like that, separated by - then those numbers become text, not numeric, and you cannot easily run calcs on text
3rd are the "numbers" always just 1 digit?

Thank you!

1. I'll keep that in mind thanks, just a direct upload as an attachment to my post?
2. Is there a way I can run a calculation on the text and turn that hyphen into a minus, so that any negative value gives me the cells I'm looking for?
3. Yes, they will always be single digits separated by a hyphen

You're great, worked perfect!

Last question - now that I have my results (a series of integers, some positive/negative, some zeros...)

How do I find all the negative numbers along a single column, refer to column A, pull the resultant value, and print all matching criteria into a single cell?

I've attached an example workbook with a typed out result of what I'd like to achieve through a function

Originally Posted by FDibbins

Try this...
B23=IFERROR(IF(LEFT(B2,1)-RIGHT(B2,1)<0,$A23,""),"")
copied down and across
This will give you the row numbers straight off

Then use this to combine them...
=SUBSTITUTE(TRIM(B23&" "&B24&" "&B25&" "&B26&" "&B27&" "&B28&" "&B29&" "&B30&" "&B31&" "&B32&" "&B33&" "&B34&" "&B35&" "&B36&" "&B37&" "&B38&" "&B39&" "&B40&" "&B41&" "&B42)," ",",")

It's a bit messy, but it works

Sorry to bring this up again, but I've hit a wall and I've asked everywhere and everyone I can think of - I just can't figure it out.

After creating the substitute formula you helped with, I then wanted to combine two cells at a time. In the attached file, I'd like to combine B44 + W44 into B45 (while removing duplicates). Then, C44 + X44 into B46, and so on and so forth. I tried codes from here and here to no avail. The second link worked with a cell range (i.e. B44:W44), however I only need two cells rather than all between.

Much appreciated!

Originally Posted by FDibbins

If you want to combine H/A and A/H, would having the answers all in 1 table (24:43) be acceptable, instead of "2" tables, 1 for H/A and 1 for A/H? If some then change the formula in B24 to this...
=IF(OR(IFERROR(LEFT(B3,1)-RIGHT(B3,1),0)>0,IFERROR(LEFT(W3,1)-RIGHT(W3,1),0)<0),$A24,"")
the summary at the bottom will then pull out the answers you want.

If you still need to show the 2 tables in 24:43, then add another helper table below, and use that same approach, you will just need to adjust the references in the summary formula

You're amazing - maybe a dozen people gave their opinions and this was the most efficient one, cheers.