Function to get matching numbers comparing two rows

[Karnik]

Hi Expert,

Request your guidance to get and matching numbers from two rows A1:G7 and B1:G7:

in a row A1:G7 1,2,3,4,5,6,7
in a row B1:G7 2,34,9,10,7,12,13

function will given results in cell A8 will show as 2,7
Thanks in advance
Karnik

[sandy666]

IMHO A1:G7 or B1:G7 is a range not row

[Richard Buttrey]

Hi,
I'll assume you mean A1:A7 and B1:B7. If not use the same technique but change the ranges

The following in C1 copied down will show the values you want. Getting them into a single cell as a string is more complicated. A UDF would probably be the simplest way. Another way would be

D1: =C1
D2 copied down:

Please Login or Register  to view this content.

Then in A8: =D7

[JohnTopley]

Assuming data in A1:G2

in H1

=IFERROR(INDEX($A$2:$G$2,MATCH(A1,$A$2:$G$2,0)),"")

dragged across will give results of 2 and 7 but in separate columns

If you want result in single cell you may need to use VBA.

[kvsrinivasamurthy]

Data1 B1:H1
data2 B2:J2
ARRAY Formula in B2, then drag across

Please Login or Register  to view this content.

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets.

[Karnik]

Hi Sandy,Richard, John & Srinivasamurthy

Thanks for stopping by my issue,
Yes it is range as per Sandy
Trying to make it more simple or clear is it possible have function instead of VB ( it's Greek to me)
using following an example

Range A1:G7 numbers are 6,8,14,16,22,29,34
Range B1:G7 numbers are 6,8,15,18,32,39,94

Is is possible to get only specific numbers like 2,6,8,12,22 if present in second B1:g7 range and are matching the first A1:G7, for above in H1 cell result will show as 6,8 ( not 6 and 8 separately cells)?
Regards
Karnik

[kvsrinivasamurthy]

See my previous post. It is a formula not VBA. See the attached file.

[jason.b75]

Range A1:G7 numbers are 6,8,14,16,22,29,34
Range B1:G7 numbers are 6,8,15,18,32,39,94

A1:G7 and B1:G7 are overlapping ranges of different sizes, so cannot be directly compared. Given that your example shows 7 numbers in 49 cells, it would appear that the information you provided is inaccurate.

An answer can only be as accurate is the information you provide.

for above in H1 cell result will show as 6,8 ( not 6 and 8 separately cells)?

That's gonna need vba, or a very long formula.

[Karnik]

Hi Srinivasamurthy,
Thanks for stopping my request again
It works when only one number is repeated, but if it has two repeat numbers the second one doe not appear
2,3,4,5,6,7,8
10,2,11,12,13,7,14
only 2 will be picked up and not 7
ideally all repeat numbers would be in one cell like: 2,7
Thanks once again
Karnik

[Karnik]

Hi Jason,

My apology! dta range were inaccurate
Yes, data are in A1:G1 and A2:G2
Srinivasamurthy suggestion works but has small bug as given above

to make it bit simple following details are repeated

Range A1:G2 numbers are 6,8,14,16,22,29,34
Range A2:G2 numbers are 6,8,15,18,32,39,94

Is is possible to get only specific numbers like 2,6,8,12,22 if present in second B1:g7 range and are matching the first A1:G7, for above in H1 cell result will show as 6,8 ( not 6 and 8 separately cells)?
Regards
Karnik

[jason.b75]

What if a number appears twice in A1:G1, but only once in A2:G2? Should the result show once, or twice?

What if the logic were reversed? A number appears once in the top row but twice in the bottom?

Or twice in both rows?

You're going to have to settle for separate cells, this isn't going to be possible in one cell without vba.

Best guess, I'm going to be offline for the rest of the day.

In I2 enter this array formula and drag right. Note, it needs to go into I2, not H2. H2 must be left Empty.

=IFERROR(HLOOKUP(TRUE,CHOOSE({1;2},IF(ISNUMBER(RANK($A$1:$G$1,$A$2:$G$2,0)),ISERROR(MATCH($A$1:$G$1,$H1:H1,0))),$A$1:$G$1),2,0),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Ohers may have different ideas, but given what you're asking for, I think that your choices will be limited to something like this, or vba.

[kvsrinivasamurthy]

Array Formula

Please Login or Register  to view this content.

[Karnik]

Hi Experts,

Array function suggested by kvsrinivasamurthy works (see below), requiring your expert guidance on this fuction as how to get results in one cell rather than individual cells

Thanks in advance
Karnik

[jason.b75]

Are you sure that formula works correctly?

The results that I see don't match your specifications.

Using a very simple test, enter these numbers into B1:J2, then check the results.

1,1,2,3,4,5,6,7,8
2,2,3,9,10,11,12,13,14

3 is duplicated but not shown in the results

Noticed that my suggestion doesn't work either.

[Karnik]

Hi Jason,
Thanks, could not try on actual work sheet , but changed few numbers in same sheet , so it appeared to be ok
Combing back to my issue , may be I could not explain properly
In any one row numbers are not repeated/duplicate .
e.g

1,2,3,4,5,6,7........ 3,4
9,10,3,4,16,17

note 3 and 4 are repeated in both rows and are duplicate , result 3,4 will be in one cell seperated by comma in top row
following function was developed by my colleague who does not work any more is now totally corrupted/missing or incomplete
if you guide me for one cell, i will copy function in all remaining 6 cells



IF(ISERROR(MATCH(B3,B4:H4,0)),"",B3)&IF(ISERROR(MATCH(C5,B4:H4,0)),""

Thanks in advance
Karnik

[jason.b75]

Noticing B3 and C5 are being used as the row to compare, even though they are not in the same row, at a guess, I would say that your formula is broken by people right clicking and deleting cells from the sheet. If #REF! appears anywhere in your existing, broken formula, then this would add more weight to the theory.

=SUBSTITUTE(TRIM(IFERROR(HLOOKUP(B3,B4:H4,1,0)&" ,","")&IFERROR(HLOOKUP(C3,B4:H4,1,0)&" ,","")&IFERROR(HLOOKUP(D3,B4:H4,1,0)&" ,",""))," ",",")

I've done 3 cells to give you an example, just repeat the bold part as many times as needed, changing underlined cell to the next one in the row.

Note that all sections need to be inside the brackets of the TRIM() function, as highlighted in red.

[Karnik]

Hi Jason,
Thanks for brilliant solution, it is totally different to previous one , very innovative.
There is only minor issue, between two numbers comma appears twice ,, see below updated one that you have solved & currently used. Any way you can have one comma? I tried modifying but due to my limited knowledge if did not work

New topic
Other guidance is, any function is possible to pull out specific numbers if found in both rows
1,2,3,4,5,6,7. .............1,2
8,9,10,1,2,3,7
If I want only find out given numbers 1, 2 are in in consecutive rows



=SUBSTITUTE(TRIM(IFERROR(HLOOKUP(C4,C5:I5,1,0)&" ,","")&IFERROR(HLOOKUP(D4,C5:I5,1,0)&" ,","")&IFERROR(HLOOKUP(E4,C5:I5,1,0)&" ,",""))&IFERROR(HLOOKUP(F4,C5:I5,1,0)&" ,","")&IFERROR(HLOOKUP(G4,C5:I5,1,0)&" ,","")&IFERROR(HLOOKUP(H4,C5:I5,1,0)&" ,","")&IFERROR(HLOOKUP(I4,C5:I5,1,0)&" ,","")," ",",")

Thanks for time and efforts, it has saved huge amount of my time
Karnik

[jason.b75]

Oops, that was my mistake!

I changed the method I was using to make the formula a bit shorter and left part of the original method in there.

The red comma shown below should not be there, it needs to be deleted from each of the seven repeated sections.

&IFERROR(HLOOKUP(D3,B4:H4,1,0)&" ,","")

Looking at the 'new topic', why only 1 and 2? 3 is also repeated in the consecutive pattern.

Could you attach another sample workbook, with several rows of random examples, and type the expected next to each row

[Karnik]

Hi Jason,
Thanks once again,
Currently i visually spot the given specific numbers if present in two consecutive rows
I was assigned find only 6 and 8 .
I will use your function and visually mark number that is asked
Example

22 25 29 23 40 42 39 21
23 31 35 39 42 43 37 8
3 15 17 28 37 44 18 4
9 12 26 33 38 39 23 44,,,,,,,9,38
9 17 19 32 37 38 22 41
8 12 13 32 37 38 6 23,,,,,,,6
9 21 24 35 44 45 10 6
5 20 27 30 32 41 40 15,,,,,,,5
5 18 21 22 36 39 7 38
15 22 28 33 37 45 21 27,,,,,,,,22,37
27 22 30 35 37 40 16 8,,,,,,,,,27,8
5 7 9 19 22 44 8 28


Warm Regards
Karnik

[jason.b75]

I just noticed another error in your previous edit to the formula, you added the extra sections after the red closing bracket, they should be before it.

For the new part, try using this as the repeated section inside the red brackets of the formula in post 16. This is to find the repeat value of 6 in the range, repeat the section as many times as needed, changing the criteria each time

IF(COUNTIF(C4:I5,6)=2,6&" ","")

Have a go at editing the formula yourself, post back if you get stuck.

[Karnik]

Hi Jason,

Thanks both functions works perfectly, thanks for saving voluminous time and human error!
Thanks once again,
Karnik