Copy data from one sheet to another using a specific criterion

nadaraza

New Member
Joined
Jul 6, 2012
Messages
20
Hi Guys,

I'm looking to copy over information from a master list of data by specifcying a specific criteria.

For example: In the info below... there's a number of Index types. I would like the code to look at the range that is all of Column C (cell by cell) and look for "AS51", if its there then to copy over the rown that it found it in to a specified tab. Loop until blank.

Essentially it's just filtering the data into a different tab by the Index.

Help please. :)

Swap CodeCounterpartyIndexSpread (bps)DateReturn AgainstReference Rate% Spread
2046BNPAS51-801/31/2007CDOR03 Index4.35143-0.8
2046DEBAS51-201/31/2007CDOR03 Index4.35143-0.2
2046GSIAS5101/31/2007CDOR03 Index4.351430
2149BNPAS51-458/17/2007CDOR03 Index4.91143-0.45
2149CBCAS51-208/17/2007CDOR03 Index4.91143-0.2

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

K0st4din

Active Member
Joined
Feb 8, 2012
Messages
377
Hi, could you attach one sample file, if possible. As far as I understand you have a table from which you want to follow column C, which can contain symbols AS51 and then transferred them to another Sheet or wrong?
Regards
 

nadaraza

New Member
Joined
Jul 6, 2012
Messages
20
Sure thing. I do have a sample file but file attachments cannot be posted on here....I can email it you?

Yep that's exactly it i have liek 1000s of line of data which contain different symbols...once i get an idea how to do it for the AS51 symbol i can just use the same code for the others i'd sort into other sheets.
 

K0st4din

Active Member
Joined
Feb 8, 2012
Messages
377
Hi friend download this file and I think that's what you need. There is little shift, but it works exactly as you want. Tried and workable.
I wish you luck -> DOX.bg -
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Hi Guys,

I'm looking to copy over information from a master list of data by specifcying a specific criteria.

For example: In the info below... there's a number of Index types. I would like the code to look at the range that is all of Column C (cell by cell) and look for "AS51", if its there then to copy over the rown that it found it in to a specified tab. Loop until blank.

Essentially it's just filtering the data into a different tab by the Index.

Help please. :)

...

Awesome. Thanks.

link to example is: example.xls downloaden

Raw Data, A1:G27...
Swap CodeCptyIndexSpread (bps)DateReturn AgainstReference Rate
1BNPAS51-451/31/2007CDOR03 Index4.35143
2DEBAS51-201/31/2007CDOR03 Index4.35143
3GSIMIB40-231/31/2007CDOR03 Index4.35143
4BNPMIB40-458/17/2007CDOR03 Index4.91143
5CBCAS51-208/17/2007CDOR03 Index4.91143
6BNPAS51-239/21/2007CDOR03 Index4.86286
7DEBAS51-359/21/2007CDOR03 Index4.86286
8BNPMIB40-210/19/2007CDOR03 Index4.82714
9DEBMIB40-910/19/2007CDOR03 Index4.82714
10JPMMIB40010/19/2007CDOR03 Index4.82714
11BNPAS51-3011/16/2007CDOR03 Index4.75857
12CBCAS51011/16/2007CDOR03 Index4.75857
13CRSAS51-3611/16/2007CDOR03 Index4.75857
14BNPMIB40-5111/19/2007CDOR03 Index4.76143
15CBCAEX-2511/19/2007CDOR03 Index4.76143
16CRSAEX-2311/19/2007CDOR03 Index4.76143
17BNPAEX-3512/21/2007CDOR03 Index4.81714
18CBCSPX-2512/21/2007CDOR03 Index4.81714
19DEBSPX-1312/21/2007CDOR03 Index4.81714
20BNPAEX-371/18/2008CDOR03 Index4.21143
21CBCAEX-301/18/2008CDOR03 Index4.21143
22CRSSPX01/18/2008CDOR03 Index4.21143
23BNPSPX-362/15/2008CDOR03 Index3.94571
24CBCSPX-512/15/2008CDOR03 Index3.94571
25DEBAEX-252/15/2008CDOR03 Index3.94571
26CBCAEX-353/20/2008CDOR03 Index3.6

<colgroup><col style="width: 56pt; mso-width-source: userset; mso-width-alt: 2673;" width="75"> <col style="width: 48pt;" width="64"> <col style="width: 49pt; mso-width-source: userset; mso-width-alt: 2332;" width="66"> <col style="width: 80pt; mso-width-source: userset; mso-width-alt: 3811;" width="107"> <col style="width: 62pt; mso-width-source: userset; mso-width-alt: 2929;" width="82"> <col style="width: 86pt; mso-width-source: userset; mso-width-alt: 4067;" width="114"> <col style="width: 94pt; mso-width-source: userset; mso-width-alt: 4465;" width="126"> <tbody>
</tbody>

AS51, with the processing in A:G...
AS51
8
Swap CodeCptyIndexSpread (bps)DateReturn AgainstReference Rate
1BNPAS51-451/31/2007CDOR03 Index4.35143
2DEBAS51-201/31/2007CDOR03 Index4.35143
5CBCAS51-208/17/2007CDOR03 Index4.91143
6BNPAS51-239/21/2007CDOR03 Index4.86286
7DEBAS51-359/21/2007CDOR03 Index4.86286
11BNPAS51-3011/16/2007CDOR03 Index4.75857
12CBCAS51011/16/2007CDOR03 Index4.75857
13CRSAS51-3611/16/2007CDOR03 Index4.75857

<colgroup><col style="width: 70pt; mso-width-source: userset; mso-width-alt: 3299;" width="93"> <col style="width: 45pt; mso-width-source: userset; mso-width-alt: 2133;" width="60"> <col style="width: 38pt; mso-width-source: userset; mso-width-alt: 1792;" width="50"> <col style="width: 81pt; mso-width-source: userset; mso-width-alt: 3840;" width="108"> <col style="width: 64pt; mso-width-source: userset; mso-width-alt: 3015;" width="85"> <col style="width: 84pt; mso-width-source: userset; mso-width-alt: 3982;" width="112"> <col style="width: 85pt; mso-width-source: userset; mso-width-alt: 4010;" width="113"> <tbody>
</tbody>

A1: AS51

A2, just enter:
Rich (BB code):
=COUNTIF('Raw Data'!$C$2:$C$27,A1)
A4, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IF(ROWS($A$4:A4)<=$A$2,SMALL(IF('Raw Data'!$C$2:$C$27=$A$1,
  ROW('Raw Data'!$C$2:$C$27)-ROW('Raw Data'!$C$2)+1),ROWS($A$4:A4)),"")
B4, just enter, copy across, and down:
Rich (BB code):
=IF($A4="","",INDEX('Raw Data'!$B$2:$G$27,$A4,MATCH(B$3,'Raw Data'!$B$1:$G$1,0)))

Repeat the process for other index values on the relevant sheets.
 

nadaraza

New Member
Joined
Jul 6, 2012
Messages
20
This works beautifully! Thanks a million for all you help Aladin & Kostdin4.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,892
Messages
5,598,702
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top