Extract information from a cell

[shoneh]

Hello all,

I have a spread sheet, see below.

Name</SPAN></SPAN>	Sales</SPAN></SPAN>	Total sales</SPAN></SPAN>
DAVE|BARRY</SPAN></SPAN>	24</SPAN></SPAN>	1</SPAN></SPAN>
BARRY|JOHN</SPAN></SPAN>	47</SPAN></SPAN>	2</SPAN></SPAN>
DAVE|JOHN</SPAN></SPAN>	205</SPAN></SPAN>	5</SPAN></SPAN>

<TBODY>
</TBODY>

What I’m looking for is a formula/ vba code that will scan through the name column and extract all the details in which for example Barry has been a participant. And the appropriate details in column b/c. </SPAN>

I was thinking and index and match, but I can’t get it to grab the specific details as it just sees it all as one word. Ideally a VBA code would be better as it runs over various row lengths.

Thanks in advance... </SPAN>

 

[Domski]

Hi,

Can you not just use Autofilter?

Dom

 

[dermie_72]

try:

=SUMPRODUCT(1*(ISNUMBER(SEARCH("Barry",A1:A1000))))

 

[Andrew Poulsom]

Or Advanced Filter with wildcards in the Criteria:

Excel 2003
	A	B	C	D	E	F	G
1	Name	Sales	Total sales	Criteria->	Name
2	DAVE|BARRY	24	1		*BARRY*
3	BARRY|JOHN	47	2	Output->	Name	Sales	Total sales
4	DAVE|JOHN	205	5		DAVE|BARRY	24	1
5					BARRY|JOHN	47	2
Sheet1

 

[Akashwani]

Hi shoneh and welcome to MrExcel.

* This is a formula solution.

Based on your sample data are these the results you would expect for Barry?..

Excel Workbook
	A	B	C	D	E	F	G	H
1	Name	Sales	Total sales		Name	Sales	Total sales
2	DAVE|BARRY	24	1		Barry	24	1
3	BARRY|JOHN	47	2			47	2
4	DAVE|JOHN	205	5
5
Sheet3

The formula in F2 needs to be entered using ctrl shift enter and NOT just enter, it can then be copied across and down. You will obviously have to change the cell references to suit your layout.

I hope that helps.

Ak

 

[shoneh]

I seem to get a #NAME? error even with the array function ctrl shift enter...

Hi shoneh and welcome to MrExcel.

* This is a formula solution.

Based on your sample data are these the results you would expect for Barry?..

Sheet3

	A	B	C	D	E	F	G	H
1	Name	Sales	Total sales		Name	Sales	Total sales
2	DAVE|BARRY	24	1		Barry	24	1
3	BARRY|JOHN	47	2			47	2
4	DAVE|JOHN	205	5
5

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 92px"><COL style="WIDTH: 112px"><COL style="WIDTH: 79px"><COL style="WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 73px"><COL style="WIDTH: 30px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula F2 {=IFERROR(INDEX(B$2:B$4,SMALL(IF(ISNUMBER(SEARCH($E$2,$A$2:$A$4)),ROW(B$2:B$4)),ROWS(F$2:F2))-ROW($A$2)+1),"")} <TBODY> </TBODY>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

The formula in F2 needs to be entered using ctrl shift enter and NOT just enter, it can then be copied across and down. You will obviously have to change the cell references to suit your layout.

I hope that helps.

Ak

 

[Akashwani]

Hi,

Can you post the formula that you are using?

Also, which version of Excel are you using?

Ak