Macro help

I have a sheet which has few data in cell. As of now I am doing with a work around. below is a brief
Please open the excel sheet sho you will get an idea.
Parent data  child data            formula to merge
testparent         testchild1                  testparent and childname testchild1
                            testchild2                 testparent and childnames testchild1,testchild2

Is it possible to take only not null values and show on a different cell. Attached the sample sheet.
Appreciate f you can add a button to show the value (without formula) in a specified cell.
I want a button to show only the not null values to the formula and copy the value(not formula) to a specified cell
ParentChild.xlsx
LVL 30
MAS (MVE)EE Solution GuideAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
We could do that but need to know where your data lies. like where is the input like you have put in Col A Col B row 2,3,4 or more ??? like would you have several parents following each others or only 1 parent and several childs ??
and will results always be on row 11 and downward ?
gowflow
0
MAS (MVE)EE Solution GuideAuthor Commented:
Only one parent info we will enter in the sheet and copy and paste the value of the cell (which has formula) to our database. It has more fields that I managed only thing is I cannot take child details as only that column has more than one row. Each time we will enter the data and clear the details after copying it to the database. Can you add a button to clear these fields as well ?so we dont need to manually delete the details every time
0
gowflowCommented:
Well too bad already done it so it take multiple families but for sure it will work as well for 1  !!! pls see the attached workbook and I copied the code for your convenience.

Option Explicit

Sub DisplayFamily()
Dim WS As Worksheet
Dim MaxRow As Long, I As Long, ResRow As Long
Dim sPrt As String, sChld As String, sBase As String

Set WS = ActiveSheet
MaxRow = WS.Range("B" & WS.Rows.Count).End(xlUp).Row
ResRow = 15

sPrt = WS.Cells(2, "A")

If sPrt <> "" Then
    '---> Clean Previous Results
    WS.Range("C:C").ClearContents
    
    '---> Process
    For I = 2 To MaxRow
        If WS.Cells(I, "A") <> "" And WS.Cells(I, "A") <> sPrt Then
            sPrt = WS.Cells(I, "A")
            ResRow = ResRow + 1
        End If
        
        
        sBase = "Parent name is " & sPrt & " and childnames are "
        Do
            If sChld <> "" Then sChld = sChld & ", "
            sChld = sChld & WS.Cells(I, "B")
            WS.Cells(ResRow, "C") = sBase & sChld
            ResRow = ResRow + 1
            I = I + 1
        Loop Until (WS.Cells(I, "A") <> "" And WS.Cells(I, "A") <> sPrt) Or I > MaxRow
        
        
        sChld = ""
        sBase = ""
        I = I - 1
    Next I
    
End If

MsgBox ("Family displayed successfully.")
End Sub

Sub ClearData()
Dim WS As Worksheet

Set WS = ActiveSheet
WS.Range("A2:C" & WS.Rows.Count).ClearContents

End Sub

Open in new window



Let me know
gowflow
ParentChild.xlsm
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MAS (MVE)EE Solution GuideAuthor Commented:
Many thanks for your work.
I want only the last one appear in the sheet.
If the parent has 1 child it show only the value with 1 child (lets say in C15)
If the parent has 2 children it show only the value with 2 children in the same cell (i.e. in C15)
If the parent has 3 children it show only the value with 3 children in the same cell (i.e. in C15)
0
gowflowCommented:
Here it is
gowflow
ParentChild.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MAS (MVE)EE Solution GuideAuthor Commented:
Many thanks for your work.
I need only one family details. Once entered we will clear the sheet after copying. Then enter the second family detals.
How to remove the second the second family codes (not important)? you can ignore this if you feel difficult
Anyway. Can you explain what are the codes you used and where if you can?
0
gowflowCommented:
It takes as many families as you want not only 2 and it is not a code for family1 then an other code for family2 etc... it loops as long as you have data it will look for the parent name then when found it will put in a variable all childs of this parent until if find a new parent then it will print the result then jump 1 line and then go to next parent until end of data. once reach it will display a message Family displayed or whatever.

So basically if you only put 1 family this code will work and display the result if you put 100 it will display them as well !!!

Hope above explanation help.
gowflow
0
MAS (MVE)EE Solution GuideAuthor Commented:
Appreciate if you tell me the code details. And I need only one parent to be entered. No need more than 1 parent
Set WS = ActiveSheet
MaxRow = WS.Range("B" & WS.Rows.Count).End(xlUp).Row
ResRow = 15

sPrt = WS.Cells(2, "A")

If sPrt <> "" Then
    '---> Clean Previous Results
    WS.Range("C:C").ClearContents
    
    '---> Process
    For I = 2 To MaxRow
        If WS.Cells(I, "A") <> "" And WS.Cells(I, "A") <> sPrt Then
            sPrt = WS.Cells(I, "A")
            ResRow = ResRow + 1
        End If
        
        
        sBase = "Parent name is " & sPrt & " and childnames are "
        Do
            If sChld <> "" Then sChld = sChld & ", "
            sChld = sChld & WS.Cells(I, "B")
            I = I + 1
        Loop Until (WS.Cells(I, "A") <> "" And WS.Cells(I, "A") <> sPrt) Or I > MaxRow
        
        WS.Cells(ResRow, "C") = sBase & sChld
        ResRow = ResRow + 1
        sChld = ""
        sBase = ""
        I = I - 1
    Next I

Open in new window

0
gowflowCommented:
I already told you Enter1 parent and it will do the job !!

Anyway I commented the step in code and I removed the process for more than 1 parent.
gowflow
ParentChild-V01.xlsm
0
MAS (MVE)EE Solution GuideAuthor Commented:
Mnay thanks Just write the code to copy the value I will add the button
0
gowflowCommented:
Sorry ?
Anything else needed from me ?
gowflow
0
MAS (MVE)EE Solution GuideAuthor Commented:
Thanks a lot
0
gowflowCommented:
Your welcome.
gowflow
0
MAS (MVE)EE Solution GuideAuthor Commented:
I need code to copy the value in C15. I will add the button to copy the value from c15
0
gowflowCommented:
Pls post a new question and put a link in here
gowflow
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.