Extract data from a list using criteria

[HughT]

Has me stumped.

Column A: Fault codes (3 digit, multiple instances in random order)
Column B: Unique serial number of part that has the fault in Column A.

Data is continuously being added to the table.

I want to use a formula to extract the serial numbers in column B into columns unique to the criteria in column A, but without gaps so that the serial numbers are in a continuous block. So for example:

A B
Fault code Serial no
123 1245
456 3071
789 7813
123 4387
789 1384
789 1179
etc

Solution
Fault code 123
1245
4387

Fault Code 789
7813
1384
1179
etc

If possible without using VBA as I have to hand this over to someone who doesn't do code.

Many thanks

HT

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G
1	Fault code	Serial no			123	456	789
2	123	1245			1245	3071	7813
3	456	3071			4387		1384
4	789	7813					1179
5	123	4387
6	789	1384
7	789	1179
Merged

Cell Formulas
Range	Formula
E2	=IFERROR(INDEX($B$2:$B$7,AGGREGATE(15,6,(ROW($B$2:$B$7)-ROW($B$2)+1)/($A$2:$A$7=E$1),ROWS($A$2:$A2))),"")

 

[HughT]

How about

	A	B	C	D	E	F	G
Fault code	Serial no

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]789[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]1245[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1245[/TD]
[TD="align: right"]3071[/TD]
[TD="align: right"]7813[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]3071[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4387[/TD]

[TD="align: right"]1384[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]789[/TD]
[TD="align: right"]7813[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1179[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]4387[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]789[/TD]
[TD="align: right"]1384[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]789[/TD]
[TD="align: right"]1179[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Merged​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IFERROR(INDEX($B$2:$B$7,AGGREGATE(15,6,(ROW($B$2:$B$7)-ROW($B$2)+1)/($A$2:$A$7=E$1),ROWS($A$2:$A2))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Very many thanks! How would it work if I wanted the data in columns A and B in a worksheet called Data, and the results in a worksheet called Lists? I have tried fiddling around with the formula but I couldn't get it to work.

HT

 

[Fluff]

Like
=IFERROR(INDEX(Data!$B$2:$B$7,AGGREGATE(15,6,(ROW(Data!$B$2:$B$7)-ROW(Data!$B$2)+1)/(Data!$A$2:$A$7=E$1),ROWS($A$2:$A2))),"")

 

[HughT]

Like
=IFERROR(INDEX(Data!$B$2:$B$7,AGGREGATE(15,6,(ROW(Data!$B$2:$B$7)-ROW(Data!$B$2)+1)/(Data!$A$2:$A$7=E$1),ROWS($A$2:$A2))),"")

Thank you so much!

HT

 

[Fluff]

Glad to help & thanks for the feedback

 

[HughT]

Sorry to ask another question. Due to the design of the spreadsheet I have had to move the columns of data across and down a bit. I have amended the formula to reflect the changes in the Data worksheet, but please could you explain what the fuinction of the last part:

ROWS($A$2:$A2)

does? I have assumed that it refers to the first cell in the column of data that holds the information I am looking for. Is this correct?

Very many thanks

HT

 

[Peter_SSs]

.. could you explain what the fuinction of the last part:

ROWS($A$2:$A2)

does?

That refers to the cell address of the first cell that holds one of these formulas. When you drag it down to the second cell it would become ROWS($A$2:$A3)

So if your first formula cell was J10, you would start with ROWS($J$10:$J10)

 

[HughT]

Many thanks for this, it's incredibly helpful! Which leads me to another problem...

The data sheet has data about faults. Column A is the fault number (multiple instances), column B is the serial number (unique) and there are more columns for Customer Name, date, progress notes etc. Data is added in rows at the bottom of the data worksheet as faults are reported.

What I want to do is extract the data into individual worksheets for each fault type, using the fault number in E1 as the lookup reference so that all the serial numbers and the Customer Name, date, progress notes etc for (say) fault 123 are on the same worksheet. There are currently about 50 fault types, potentially with up to 1000 individual serial numbers (so 1000 rows of data) although some only have a few. There are currently 6 columns of data so the number of cells per sheet containing formulas is 1000 rows x 6 columns, so 6000 formulas per worksheet, x 50 worksheets = 30,000 volatile formulas. Not surprisingly, it crashed!

I initially tried using VLOOKUP, using the serial number column derived by the formula as the lookup reference for each row, but this had the effect of creating errors in the Index formula and didn't work, and presumably would have the 'too many formulas' issue even if it did work.

I am wondering if this is completely the wrong approach, and should I be doing something different? I tried using Pivot Tables but these are not liked by my colleagues as they are not at all user friendly. Would some kind of Table Array work?

Any thoughts gratefully accepted!

HT

 

[Fluff]

The formula I supplied is not volatile and (whilst I'm primarily VBA based) I wouldn't have said that 1000x6 per sheet was excessive.
What are you other formulae?