How to extract data from 2 columns?

[saltwater]

Hello,

I have a list of part numbers (some are repeated) in column A

In column B I have quantities.

I need to extract all part numbers in column A that are part number 111111 along with the adjacent quantity in column B.

Column A Column B
111111 45
123456 11
789654 12
123456 10
111111 55
111111 65

The data I am looking for in the new cells should like this...
Column C Column D
111111 45
111111 55
111111 65

How can I accomplish this?

 

[Weazel]

Re: How to extract data from 2 colmuns?

maybe something like...

A9 - value you are looking up

B9 =COUNTIF(A2:A7,A9)

A10=IF(ROWS($A$10:A10)>$B$9,"",INDEX(A$2:A$7,SMALL(IF($A$2:$A$7=$A$9,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(A$10:A10)))) control shift enter

B10=IF(ROWS($A$10:B10)>$B$9,"",INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=$A$9,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(B$10:B10)))) control shift enter

Row\Col	A​	B​
1​	ColumnA	ColumnB
2​	111111​	45​
3​	123456​	11​
4​	789654​	12​
5​	123456​	10​
6​	111111​	55​
7​	111111​	65​
8​
9​	111111​	3​
10​	111111​	45​
11​	111111​	55​
12​	111111​	65​
13​
14​
15​

<tbody>
</tbody>

 

[CalcSux78]

You can use an Index - Match Array to retrieve the Nth match of the listed part#.

Excel 2010
	A	B	C	D
1	111111	45	111111	45
2	123456	11	111111	55
3	789654	12	111111	65
4	123456	10
5	111111	55
6	111111	65
Sheet3

Cell Formulas
Range	Formula
D1	{=IF(ISERROR(INDEX(A:B,SMALL(IF(A:A=$C1,ROW(A:A)),ROW(1:1)),2)),"",INDEX(A:B,SMALL(IF(A:A=$C1,ROW(A:A)),ROW(1:1)),2))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Here's an article you might read up on.

 

[saltwater]

Thank you both for your responses.

Calsux78, how come my cell returns blank with this version?

=IF(ISERROR(INDEX($S$801:$T$1101,SMALL(IF($S$801:$S$1101=$M1103,ROW(S801:S1101)),ROW($S$801:$T$1101)),2)),"",INDEX($S$801:$T$1101,SMALL(IF($S$801:$S$1101=$M1103,ROW($S$801:$S$1101)),ROW(S801:T1101)),2))

I do enter with (Ctrl+Shift+Enter) no issues there.