Fill sheet similar pivot, but not pivot...

sal21

Active Member
Joined
Apr 1, 2002
Messages
291
I would want with a macro use the sheet STATISTICA similar a pivot (but repeat i dont want a pivot).
Bsed on column H and column D of GAF, scanning in all cells of columns H and if is found a value present in one sheet CORPORTAE, RETAIL_POE, PUBB_AMM, LARGE_COR, SCARTI, (have the same range for all sheet G2:G500) add 1 in cell refred column D of GAF and column A of STATISTICA fill the cell in STATISTICA based column D...

Example:
cell H2 in GAF contain 78 this value is present only in sheet RETAIL_POE in range G2:G500 put 1 in C25 of STATISTICA because in A25 is present 4549...

cell H3 in GAF contain 78 this value is present only in sheet RETAIL_POE in range G2:G500 put 1 in C31 of STATISTICA because in A31 is present 4561...

cell H4 in GAF contain "" put 1 in G25 of STATISTICA because in A25 is present 4549...

cell H5 in GAF contain 78 this value is present only in sheet RETAIL_POE in range G2:G500 put 1 in C30 of STATISTICA because in A30 is present 4558...
........

cell H51 in GAF contain 11 this value is present only in sheetCORPORATE in range G2:G500 put 1 in B2 of STATISTICA because in A2 is present 4500...

naturally if the value in H is found for many matching ad 1 the the old value stored

Hope i am clear...

here my file: http://www.mytempdir.com/991542
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi -
try;
Code:
Sub table_me()
Dim i, ii, iii As Long
Application.ScreenUpdating = False
Sheets("GAF").Select
For i = 2 To Range("h" & Rows.Count).End(xlUp).Row
    For ii = 3 To Sheets.Count
    iii = Sheets(ii).Range("g" & Rows.Count).End(xlUp).Row
        With Sheets(ii).Range("g2:g" & iii)
            Set c = .Find(Sheets("GAF").Cells(i, "h").Value, , , xlWhole)
                If Not c Is Nothing Then
                    With Sheets("STATISTICA").Columns("a")
                        Set fc = .Find(Sheets("GAF").Cells(i, "d").Value, , , xlWhole)
                            If Not fc Is Nothing Then
                                fc.Offset(, ii - 2) = 1
                            End If
                    End With
                End If
        End With
    Next
Next
Sheets("STATISTICA").Select
Application.ScreenUpdating = True
MsgBox "Processing Done!", vbInformation + vbOKOnly, "End!"
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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
Back
Top