Searching a cell value in another sheet and copying parent value which it belongs to

[Pankaj Sonawane]

Hi,

I need to search for a cell value in another sheet and copy the parent value of the cell which it reports into. Below are the steps I am following but not able to put in in macro as I am new to writting macro and getting stuck at some stages.

Request you to go through the following steps and kindly help me resolving the same.

I am attaching two excel sheet which I have mentioned in my Steps and the same steps are mentioned in the excel sheet called "Copy of Extract"

Step 1 Select value of cell B2 in worksheet "Split Data" which is in workbook "Copy of Extract"

Step 2 Check if value of cell B2 is present in column A of 1st worksheet "Bat" which is in workbook "Copy of Mapping Details"

Step 3 If value of cell B2 is present in column A of 1st worksheet "Bat" then start searching for the first 1 (number 1) in column C in upward direction
NOTE: (For E.g. If B2 i.e. NNN is present on row no. 15, start searching column c from row 15(from Cell C15 to upward direction) to upward direction and give me the value of cell A where first 1 is present "In this case it will be at row 4 and value returned will be CCC)

Step 4 Copy the value of cell A where first "1" is present
NOTE:In this case: Copy the value "CCC"

Step 5 Copy the value of cell A in cell B2 of worksheet "Data"
NOTE:This cell is marked in RED

Step 6 if value of cell B2 is not present in column A of 1st worksheet "Bat" then check
if value of cell B2 is present in column A of 2nd worksheet "Ball"

Step 7 If value of cell B2 is present in column A of 2nd worksheet "Ball" then start searching for the first 1 (number 1) in column C in upward direction (and if Value of B2 does not exist in sheet "Ball" search it in 3rd sheet "Stump" and if not present then search it in 4th sheet "Pad")
NOTE;If Value is not present in column A of 1st sheet, Check in column A of 2nd sheet, if still not present then, Check in column A of 3rd sheet and so on till the last sheet is present and till value is not found. If value is not found in any of the sheets give "NA" in the cell in sheet "Data" as an output for the cell.

Step 8 Follow Step 4

Step 9 Follow Step 5

Step 10 Now go to cell C2 of worksheet "Split Data"
NOTE: We will check the cells horizontally one by one till the "Sr Nos" are present in column A of workbook "Copy of Extract" and once the Sr No ends macro will stop/end

Step 11 Check if cell C2 has any value

Step 12 If value is present follow Step1, 2, 3, 4, 5 to copy the value of cell A in cell C2 of sheet "Data" from sheet 1, 2, 3 or 4 which are in workbook "Copy of Mapping Details"

Step 13 If cell C2 is empty jump to cell B3

Step 14 Follow Step 1,2,3,4,5 for value of cell B3 and copy the value of cell A in cell B3 of sheet "Data" as mentioned above in step 5

Step 15 Then go to C3, follow steps 1,2,3,4,5 and then for D3 and so on till the cell
beside last cell is empty

Step 16 Continue this search of cells of sheet "Split Data" till last row. In this case it will be row 11,Cell G11
NOTE: Please see that rows and columns will not be limited upto 11 rows or 10 columns. There can be 1000's of rows and colmns for which we need to follow the search and copy process. The row nos. and column nos. can change based on the data available. So, I need a macro which can be used for any no. of row and columns

I humbly hope to receive a great solution as soon as possible.

Regards,
Pankaj

[Pankaj Sonawane]

"Bump No Response"

[Pankaj Sonawane]

"Bump No Response"

Can anyone help me out Please.

Regards,
Pankaj

[Pankaj Sonawane]

"Bump No Response"

Just FYI..I have described the steps in detail so that it becomes easy to implement the macro. Its too long to read but short for a expert.

Hoping for some help.

Regards,
Pankaj

[StephenR]

I think you haven't got a response because your post is very long, and most people don't have the time and/or inclination to read such large blocks of text. If possible, try to break it down into different tasks. In the meantime I will try to have a look, as currently confined indoors.

EDIT: having had a read, it's not as complicated as it first looked.

[StephenR]

I checked a couple and this seems to work, but you'd better check thoroughly.

Sub x()
  
Dim rFind As Range, rFind2 As Range, r As Range, wbSearch As Workbook
Dim rSearch As Range, i As Long, vSheets

Set wbSearch = Workbooks("Copy of Mapping Details.xls")
With ThisWorkbook.Sheets("Split Data")
    Set rSearch = .Range("A1").CurrentRegion
    Set rSearch = rSearch.Offset(1, 1).Resize(rSearch.Rows.Count - 1, rSearch.Columns.Count - 1)
End With

vSheets = Array("Bat", "Ball", "Stump", "Pad")

For Each r In rSearch
    For i = LBound(vSheets) To UBound(vSheets)
        With wbSearch.Sheets(vSheets(i))
            Set rFind = .Columns(1).Find(What:=r, lookAt:=xlWhole, SearchOrder:=xlByRows, _
                                         SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If Not rFind Is Nothing Then
                Set rFind2 = .Columns(3).Find(What:=1, after:=rFind.Offset(, 2), lookAt:=xlWhole, SearchOrder:=xlByRows, _
                                              SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
                If Not rFind2 Is Nothing Then
                    rFind2.Offset(, -2).Copy ThisWorkbook.Sheets("Data").Cells(r.Row, r.Column)
                    Exit For
                End If
            End If
        End With
    Next i
Next r
     
End Sub

[Pankaj Sonawane]

Hi Stephen,

Thank you very much for replying back. This macro works upto a great extent, however if you see its copying a specific cell value i.e. A73 from worksheet "Bat" in all empty cells.

For E.g.: If you look at sheet "Split Data" in workbook "Copy of Extract" cell b2 had a value "NNN" and after that cells c2 to k2 are empty, but if you see in Sheet "Data" in the same workbook cells c2 to k2 are not empty which should be empty. The macro is copying a specific cell A73 from worksheet "Bat" which is in workbook "Copy of Mapping Details" in all empty cells.

Could you look into this please. I tried to change the code but was unable to find the reason. I have attached both the sheets and marked the incorrect/empty cells yellow for your reference.

Thans again!!!

Regards,
Pankaj

[Pankaj Sonawane]

Hi Stephen,

I am working on preparing a macro for the attached sheets . Below is the explanation in brief so that I don’t waste your time on this:

Step1: Start from column E, cell E2. First search value "2" (value before comma) in one of the sheets of workbook "Copy of Mapping Details". After finding look into which "level 2(L2 column name)" it is reporting to (In this case 2 is L3 and reports to 1 which is L2). Then see that is "1" present in columnd D, cell D2 or not. If "1" is present in column D, cell D2 then search 2nd value "JJJ"(value after comma) in in one of the sheets of workbook "Copy of Mapping Details". If present find L2 value for it (in this case it is CCC).

Step 2: Then see that is "CCC" present in columnd D, cell D2 or not. If "CCC" is present in column D, cell D2 then go to next cell of column E i.e. E3. If E3 is empty then goto E4 and If E4 is empty goto E5

Step3: Follow same search condition for the values EEE, evv & fgdvdf. Now if Value "fgdvdf" in E5 reports to "wqwe4"(L2) and it is not present in column D, cell D5 add "wqwe4" in D5 and mark it RED. Follow thse steps till column E has no data below.

Step 4: After Serching and adding data (we can say mapping data to column C "L2") to column D in "Copy of Extract1" we need to split column D and copy rest of the columns as it is(As shown in sheet "Split Data")

I am attaching the sheets for your reference. Hope you will take out some time and help me on this. Let me know if you want some mre details on this from my side.

Regards,
Pankaj

[StephenR]

Pankaj - I will take a look re your comments in post 7. Is your last post a new question, or related to original?

[StephenR]

Try this. I have assumed that if the starting cell is blank, you want the result to be blank.

Sub Mapping()
  
Dim rFind As Range, rFind2 As Range, r As Range, wbSearch As Workbook
Dim rSearch As Range, i As Long, vSheets

Set wbSearch = Workbooks("Copy of Mapping Details.xls")
With ThisWorkbook.Sheets("Split Data")
    Set rSearch = .Range("A1").CurrentRegion
    Set rSearch = rSearch.Offset(1, 1).Resize(rSearch.Rows.Count - 1, rSearch.Columns.Count - 1)
End With

vSheets = Array("Bat", "Ball", "Stump", "Pad")

For Each r In rSearch
    If r <> vbNullString Then
        For i = LBound(vSheets) To UBound(vSheets)
            With wbSearch.Sheets(vSheets(i))
                Set rFind = .Columns(1).Find(What:=r, lookAt:=xlWhole, SearchOrder:=xlByRows, _
                                             SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
                If Not rFind Is Nothing Then
                    Set rFind2 = .Columns(3).Find(What:=1, after:=rFind.Offset(, 2), lookAt:=xlWhole, SearchOrder:=xlByRows, _
                                                  SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
                    If Not rFind2 Is Nothing Then
                        rFind2.Offset(, -2).Copy ThisWorkbook.Sheets("Data").Cells(r.Row, r.Column)
                        Exit For
                    End If
                End If
            End With
        Next i
    End If
Next r
     
End Sub

[Pankaj Sonawane]

Hi Setphen,

Thank you very much to take some time out on this.

My last post is the macro am working on. Please see if you have time to work on and it is related to the original one only.

Below is the explanation in brief:

Step1: Start searching from column E, cell E2 in workbook "Copy of Extract1", sheet "Base Data" . First search value "2" i.e. cell E2(value before comma) in column A of one of the sheets of workbook "Copy of Mapping Details"(we need to look out for the value in clumn A of all sheet present in workbook "Copy of Mapping Details" i.e. Bat, Ball, Stump & Pad) . After finding look into which "level 2 (L2 column name)" it is reporting to (In this case 2 is level3(L3), present in sheet "Bat" and reports to 1 which is L2). Then see that is "1" present in column D, cell D2 of sheet "Base Data" in workook "Copy of Extract1" or not. If "1" is present in column D, cell D2 then search 2nd value "JJJ" (value after comma) in column A of one of the sheets of workbook "Copy of Mapping Details". If present find L2 value for it (in this case it is CCC).

Step 2: Then see that is "CCC" present in columnd D, cell D2 or not. If "CCC" is present in column D, cell D2 then go to next cell of column E i.e. E3. If E3 is empty then goto E4 and If E4 is empty goto E5

Step3: Follow same search condition for the values EEE, evv & fgdvdf in E5(sheet "Base Data") . Now Value "fgdvdf" in cell E5 reports to "wqwe4"(L2) in workbook "Copy of Mapping Details", sheet "Ball".Check if it is present in column D, cell D5 and if not present, then add "wqwe4" in D5 and mark it RED. Follow these steps till column E has no data below.

Step 4: After Searching and adding data (we can say mapping column E of sheet "Base Data" in workbook "Copy of Extract1" to column C "L2" of any of the sheets in workbook "Copy of Mapping Details" and copying data in column D which is not present in column D) we need to split column D and copy rest of the columns as it is(As shown in sheet "Split Data")

NOTE: For E.g. if we find a value present in sheet "Bat" it can be determined 2/3/4/5/6 as no.1 is present in front of each value, under the column L1 to L6. Here I am trying to map Level 3/4/5/6 to Level 2. So, if we find any level 3/4/5/6 value in any sheet, we need to find column c in upward direction to find where no.1 is present where level 3/4/5/6 reports into and copy that value in column D of workbook "Copy of Extract1" and mark it red, if found L2 value already present in column D then do not copy and find next value. After this split the data in column D of worksheet "Base Data")

Stephen: Your code in post 10 works fine like butter. Now as we have found out the level 2 for the values I need to add some more steps to this which I have mentioned above in detail. We need to find that is the level 2 value present in column D of workbook "Copy of Extract1", sheet "Base Data", if present find another value and check again if ite is present in column D, if not then copy it in column D (seperating it by comma,no space after comma). After this I need to split column D and copy rest of the rows/columns.cells as it is. I have attahched the sheets for your reference where you will get an idea what I am trying to do

Let me know if you want more details from my end.

Regards,
Pankaj

[StephenR]

Pankaj - first, have we sorted out the original question? Second, if you have a new question, I suggest you start a new thread, and I will take a look.

[Pankaj Sonawane]

Ok!!! we have sorted out the original one...I am marking this as SOLVED and will start a new one...Thanx for suggestion....

The new thread name is: "Mapping a cell value from another sheet". Please have a look into it.

Regards
Pankaj