vba help

Question

Good morning,

I am trying to get a macro to do the following. Find all the failures on tab 1 in column E and then find all the errors on tab 2 in column C & D that correspond to the errors on tab 1. For every line in sheet 1, there are 10 lines on sheet 2. In the example below there are 3 lines on sheet 1 representing 0.3 of a mile (each line represents 0.10 miles). Sheet 2 would have (but are not shown) 30 lines of information each representing 0.01 miles. Sheet 3 would only show the failure information.

sheet1 (0.10 miles)

    A            B                      C             D                     E (average of A and B)

1 0.1 0.2 2.8 3.5 3.25

2 0.2 0.3 4.0 4.1 4.1

3 0.3 0.4 4.2 3.5 3.85

Sheet 2 (0.010 miles)

      A           B              C            D                   

1 0.11 0.12 3.5 3.2

2 0.12 0.13 4.5 4.5

3 0.13 0.14 4.3 3.2

4 0.14 0.15 3.1 3.5

Sheet 3 summary

      A           B            C                       D             E             F        G

1 0.1 0.2 3.25 0.011 0.012 3.5 3.2

2 0.013 0.014 4.3 4.2

3 0.3 0.4 3.85

4

The information for Column A, B, & C comes from sheet 1. Row 3 on sheet 3 is showing a failure but I didn't show the 0.01 mile lines on sheet 2 to save room. Information for Columns D-G comes from sheet 2. Hope this makes sense.

Eric

Answer 1

Just a few things that I hope you can correct.

Cells A43:D46 represents 528 feet where there is a deficiency

Cells G43:J46 represents 52 foot section of A43:D46 which has a deficiency.

For example from the picture above

Line A43 needs to match up with line G43 and G44

Blank line then,

Line A44 needs to match up with line G45 and G46.

I see you have more data on the right then needed. I only need the information on the right that matches the 528 foot section on the left.

I hope I am not confusing you ........

Answer 2

If I understood you correctly then you want the results in the green areas!?

We can do this with a formula, but I get a bit more results as in your example.

According to which criterion should this result be further reduced?

A43:
=LET(

From,'IRI (3)'!B2:B97,

To,'IRI (3)'!C2:C97,

Lane,'IRI (3)'!D2:D97,

RnLeft,'IRI (3)'!I2:I97,

RnRight,'IRI (3)'!J2:J97,

Avg,BYROW(RnLeft:RnRight, LAMBDA(x, AVERAGE(x))),

Data, HSTACK(Lane,From,To,Avg),

Res,FILTER(Data, Avg<4, "(none)"),

Res)

G43:
=LET(

From,'IRI (2)'!B2:B950,

To,'IRI (2)'!C2:C950,

Lane,'IRI (2)'!D2:D950,

RnLeft,'IRI (2)'!I2:I950,

RnRight,'IRI (2)'!J2:J950,

Avg,BYROW(RnLeft:RnRight, LAMBDA(x, AVERAGE(x))),

Data, HSTACK(From,To,RnLeft,RnRight),

Res,FILTER(Data, Avg<3.5, "(none)"),

Res)

Andreas.

Answer 3

Not sure if you will have access.

No, the link did not work, I can not log in into your sharepoint. Try to create a public link

Share SharePoint files or folders

Or upload the file into my dropbox.

Microsoft Answers Community Public Request - Dropbox

Andreas.

Answer 4

Above pic is sheet 1 in the example and tab IRI (3) in the workbook

The above pic is sheet 2 in the example or tab IRI (2) in the workbook

Pic 3 would be the summary sheet from the example or tab RN in the workbook

Does this help? I do not know how to upload the workbook. Not sure if you will have access. Let me know if you need anything more from me. Thank you in advance.....

example.xlsx

Answer 5

We need to see your (sample) file.
Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

Andreas.