Solved

Macro help

Posted on 2014-09-12
15
260 Views
Last Modified: 2014-09-12
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
0
Comment
Question by:-MAS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 40318967
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
 
LVL 26

Author Comment

by:-MAS
ID: 40318978
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
 
LVL 30

Expert Comment

by:gowflow
ID: 40318996
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 26

Author Comment

by:-MAS
ID: 40319012
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
 
LVL 30

Accepted Solution

by:
gowflow earned 500 total points
ID: 40319020
Here it is
gowflow
ParentChild.xlsm
0
 
LVL 26

Author Comment

by:-MAS
ID: 40319032
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
 
LVL 30

Expert Comment

by:gowflow
ID: 40319046
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
 
LVL 26

Author Comment

by:-MAS
ID: 40319078
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
 
LVL 30

Assisted Solution

by:gowflow
gowflow earned 500 total points
ID: 40319108
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
 
LVL 26

Author Comment

by:-MAS
ID: 40319127
Mnay thanks Just write the code to copy the value I will add the button
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40319129
Sorry ?
Anything else needed from me ?
gowflow
0
 
LVL 26

Author Closing Comment

by:-MAS
ID: 40319131
Thanks a lot
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40319141
Your welcome.
gowflow
0
 
LVL 26

Author Comment

by:-MAS
ID: 40319148
I need code to copy the value in C15. I will add the button to copy the value from c15
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40319374
Pls post a new question and put a link in here
gowflow
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question