Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Herman
 
Posts: n/a
Default extract specific info from cells in a column

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
  #2   Report Post  
Max
 
Posts: n/a
Default extract specific info from cells in a column

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 ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Herman" wrote in message
...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro Help: Concatenate Populated Cells in Column A TJM Excel Discussion (Misc queries) 3 June 11th 05 11:25 AM
Need a 1 Column multiplication formula for 2600 cells. How? Buckle Excel Worksheet Functions 1 March 25th 05 05:17 PM
Average the Last Five Cells in a Column Warrior Princess Excel Worksheet Functions 3 March 16th 05 03:12 PM
calculate which cells in column A will give me the total of column Ken Excel Worksheet Functions 4 January 6th 05 07:25 AM
How do I ROUNDUP a Column of cells on a worksheet template? house mouse Excel Worksheet Functions 2 December 16th 04 07:49 PM


All times are GMT +1. The time now is 11:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"