If Statement and Loop

slamcity

New Member
Joined
Jul 17, 2006
Messages
19
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!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

New Member
Joined
Jul 17, 2006
Messages
19
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!



 

Watch MrExcel Video

Forum statistics

Threads
1,133,656
Messages
5,660,153
Members
418,555
Latest member
Ike Andoit

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