extract specific info from cells in a column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

how do I extract specific info within a cell ie 73AP9 and 73AP7 from column
ranged B1:B200 on sheet 1 and have show up on sheet 2 in a specific row
under 2 separate columns 1 for each entry, ie row is titled 73A looking to
put the 73AP9 and 73AP7 in their own column within the row label 73A
 
Venturing a guess here as to what's wanted ..

Link to demo file with sample construct at:
http://www.savefile.com/files/3124085
Extracting Specific Info From Cells In Col_Herman

Assuming source data in Sheet1,
in col B, textheader in B1, data from B2 down

Label
73AP9
73AP7
74AP5
74AP3
73AP8
74AP9
etc

Using 2 empty cols to the right, say, cols D & E
Enter the captions in D1:E1 : 73A, 74A

Put in D2: =IF(ISNUMBER(SEARCH(D$1,$B2)),ROW(),"")
Copy D2 across to E2, fill down to say, E300,
to cover the max expected data in col B

In Sheet2
---------
With captions entered in A1 across: 73A, 74A

Put in A2:

=IF(ISERROR(SMALL(OFFSET(Sheet1!$C:$C,,
MATCH(A$1,Sheet1!$D$1:$E$1,0)),ROWS($A$1:A1))),"",
INDEX(Sheet1!$B:$B,MATCH(SMALL(OFFSET(Sheet1!$C:$C,,
MATCH(A$1,Sheet1!$D$1:$E$1,0)),ROWS($A$1:A1)),
OFFSET(Sheet1!$C:$C,,MATCH(A$1,Sheet1!$D$1:$E$1,0)),0)))

Copy A2 across to B2, fill down to B300
(cover the same range size as was done in Sheet1's cols D & E)

Sheet2 will return the desired results from Sheet1 neatly under each
caption, with all results bunched cleanly at the top. For the sample data,
we'd get:

73A 74A
----------
73AP9 74AP5
73AP7 74AP3
73AP8 74AP9
(blank rows below)

Adapt to suit ..
 
Back
Top