Solved

Macro help

Posted on 2014-09-12
15
253 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
  • 8
  • 7
15 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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 24

Author Comment

by:-MAS
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 24

Author Comment

by:-MAS
Comment Utility
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 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
Here it is
gowflow
ParentChild.xlsm
0
 
LVL 24

Author Comment

by:-MAS
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 24

Author Comment

by:-MAS
Comment Utility
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 29

Assisted Solution

by:gowflow
gowflow earned 500 total points
Comment Utility
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 24

Author Comment

by:-MAS
Comment Utility
Mnay thanks Just write the code to copy the value I will add the button
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Sorry ?
Anything else needed from me ?
gowflow
0
 
LVL 24

Author Closing Comment

by:-MAS
Comment Utility
Thanks a lot
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Your welcome.
gowflow
0
 
LVL 24

Author Comment

by:-MAS
Comment Utility
I need code to copy the value in C15. I will add the button to copy the value from c15
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Pls post a new question and put a link in here
gowflow
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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 …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now