ExcelNewbie011
New Member
- Joined
- May 23, 2011
- Messages
- 2
Hi all, this is my first post so I apologise for my lack of forum lingo and knowledge!
I have a situation where I have a rather large spreasheet, that we populate each week with 35 columns and 20,000 rows worth of data. At the moment we have to manually go through this data and look at certain columns for particular key words that we are looking for, or for letters in certain reference numbers that have meaning to us.
If a keyword exists, or the letter exists in the reference number, then we cut the entire row and paste it from Sheet 1 to Sheet 2. We manually enter in the column headers in Sheet 2 before we begin. We do this manually over the entire 20,000 records! What I would like to have, it a situation where excel does this searching for us!
What I am after is a macro that;
- Will automatically take the column headers from Sheet 1, Row 1 and paste them into Sheet 2, Row 1.
- Can search down a column (that will always be the same eg: 'C') for a particular letter (alphabet) that is ALWAYS at the front of the reference. eg: X99887766. It will always be formatted this way, with no gaps. We are not interested in the numbers, only the letter at the front. We are interested in 3 different letters, out of 7 that can exist in these references. If one of the 3 letters we want exists in the reference then we want excel to copy the entire row where that letter exists and paste the row into Sheet 2.
- Can search down multiple pre-determined columns (that will always be the same columns eg: E, G, I) for pre-determined keywords that we have 'hard-coded' into the macro. (there could be up to 100 key words that we would use for this. These keywords could change over time as well, so will need to be able to 'update' the macro as necessary). The Cells in question will not contain just one word, so the macro will need to be able to search cells that contain multiple words in them (eg: New Zealand Organisation of Federated Farmers) We might be interested in 'Organisation' and 'Farmers' from this cell. It will then copy that entire row from Sheet 1 and paste it in Sheet 2
- This is a 'would like to have' but is not absolutely necessary! - In the records sent to Sheet 2 - it would be great if the macro could Highlight the keyword, or letter reference that meant the record was sent to Sheet 2 from Sheet 1 - that way we can home in on exactly the point we are interested in, rather than having to still go through and look for why we are interested in these records.
It is guaranteed that there will be more than one instance of the same keyword or letter reference in the 20,000 pieces of data, the macro needs to be able to 'grab' multiple instances of the same keyword/number etc. Eg: in one spreadsheet we have 17 reference numbers that all contain the letter F at the beginning, these are spread throughout the 20,000 rows - we would want all 17 of these rows where F exists to be transferred.
It needs to perform both of these tasks simultaneously, with one click - I work with a rather technology-averse group of people, so the end product needs to be user-friendly
I hope this is enough information for one of the Macro Pro's to sink their teeth into! Because of the commercial nature of the information I'm dealing with, unfortunately I cannot upload an example spreadsheet of what we are using.
Many thanks in advance,
Dan
I have a situation where I have a rather large spreasheet, that we populate each week with 35 columns and 20,000 rows worth of data. At the moment we have to manually go through this data and look at certain columns for particular key words that we are looking for, or for letters in certain reference numbers that have meaning to us.
If a keyword exists, or the letter exists in the reference number, then we cut the entire row and paste it from Sheet 1 to Sheet 2. We manually enter in the column headers in Sheet 2 before we begin. We do this manually over the entire 20,000 records! What I would like to have, it a situation where excel does this searching for us!
What I am after is a macro that;
- Will automatically take the column headers from Sheet 1, Row 1 and paste them into Sheet 2, Row 1.
- Can search down a column (that will always be the same eg: 'C') for a particular letter (alphabet) that is ALWAYS at the front of the reference. eg: X99887766. It will always be formatted this way, with no gaps. We are not interested in the numbers, only the letter at the front. We are interested in 3 different letters, out of 7 that can exist in these references. If one of the 3 letters we want exists in the reference then we want excel to copy the entire row where that letter exists and paste the row into Sheet 2.
- Can search down multiple pre-determined columns (that will always be the same columns eg: E, G, I) for pre-determined keywords that we have 'hard-coded' into the macro. (there could be up to 100 key words that we would use for this. These keywords could change over time as well, so will need to be able to 'update' the macro as necessary). The Cells in question will not contain just one word, so the macro will need to be able to search cells that contain multiple words in them (eg: New Zealand Organisation of Federated Farmers) We might be interested in 'Organisation' and 'Farmers' from this cell. It will then copy that entire row from Sheet 1 and paste it in Sheet 2
- This is a 'would like to have' but is not absolutely necessary! - In the records sent to Sheet 2 - it would be great if the macro could Highlight the keyword, or letter reference that meant the record was sent to Sheet 2 from Sheet 1 - that way we can home in on exactly the point we are interested in, rather than having to still go through and look for why we are interested in these records.
It is guaranteed that there will be more than one instance of the same keyword or letter reference in the 20,000 pieces of data, the macro needs to be able to 'grab' multiple instances of the same keyword/number etc. Eg: in one spreadsheet we have 17 reference numbers that all contain the letter F at the beginning, these are spread throughout the 20,000 rows - we would want all 17 of these rows where F exists to be transferred.
It needs to perform both of these tasks simultaneously, with one click - I work with a rather technology-averse group of people, so the end product needs to be user-friendly
I hope this is enough information for one of the Macro Pro's to sink their teeth into! Because of the commercial nature of the information I'm dealing with, unfortunately I cannot upload an example spreadsheet of what we are using.
Many thanks in advance,
Dan
