If Statement and Loop

[slamcity]

Hello All,

I know this may be simple, but I am trying to create a macro that will copy certain rows based on the value in column A. Here are some details:

I have 2 separate workbooks, one with 6 separate sheets of 6 months of data (sheet1) (which I will need to reference against) and another spreadsheet where the data manipulation will occur (sheet2).

I will have data pasted and sorted descending by column A (price) and ascending by column B (part numbers) on sheet2 (so that the top of the sheet will reflect the highest prices but keep the part numbers together). At this point, I will need the macro to be run. Here are the parameters:

I need to have only the top ten part numbers with highest price remaining. However, if the part number (column B) is present in any of the 6 worksheets in Sheet1, I do not want these rows to be included in the list.

I know that this will take some sort of If Statement and Loop, but am not exactly sure how to set it up. Any help would be awesome. Thanks!

 

[acw]

Hi

Can you clarify your workbook structures. What is the name of the data manipulation workbook that contains sheet2.

With the source data workbook, do you have 1 workbook with 6 sheets (1 for each month's data), or 1 workbook with 1 sheet with 6 month's data or do you have 6 workbooks, one for each month.

Either way, what are the workbook names and sheet names.


Tony

 

[slamcity]

My two workbooks' names are: 1) Adj Template and 2) Frequent Adjs

1) Adj Template will be the workbook where the macro will be written and where the manipulation will occur which contains Sheet2. This sheet has two columns that I will be using, column A (Times Adj) and column C (Part). The number of times that a Part is listed in the spreadsheet will be the number represented in column A (in other words, the count of column C). Here is an example:

A C

Times Adj Part

4 A1245
4 A1245
4 A1245
4 A1245

What I need to do is keep only a top ten list of rows whose part numbers have the highest number of Times Adj (column A) as long as these Parts are not located in any of the 6 sheets in Freq Adjs. The Parts will be kept in blocks as they will be sorted by "Times Adj" and "Part" columns.

2) Frequent Adjs is the workbook which I will reference against and has six separate sheets, each one representing a previous month (Jan, Feb, Mar, Apr, etc.)

The final result should look similar to this:

A C

Times Adj Part

4 A1245
4 A1245
4 A1245
4 A1245
4 B2356
4 B2356
4 B2356
4 B2356
3 C5789
3 C5789
3 C5789

and so on for ten Parts so that each Part will be in blocks of 3, 4, etc...

I'm not sure if this made anything more clear, so let me know if I confused you more or if you have any more questions. Thanks again!