?
Solved

Macro help

Posted on 2014-09-12
15
Medium Priority
?
262 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 31

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 27

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 31

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 27

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 31

Accepted Solution

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

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 31

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 27

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 31

Assisted Solution

by:gowflow
gowflow earned 2000 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 27

Author Comment

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

Expert Comment

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

Author Closing Comment

by:☠MAS☠
ID: 40319131
Thanks a lot
0
 
LVL 31

Expert Comment

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

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 31

Expert Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

752 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