Posts by paspuggie48

Agree VjBox, maybe my data was wrong because what you said is correct and a logic should apply. Assume Flywheel is Level 1. However, I would ignore the dots and can only suggest to use the blankcells as represent the range (?) as that is the constant regardless of level or not.

There are dynamic blank cells in the Hierarchy column ie.e 1, 2, 3 or whatever. Would not finding the first instance of a blank cell in the Hierarchy column, then to the end of that range until a cell that contains a value...then concatenate that dynamic range with possibly using offset? Just thinking aloud here as I lack the obvious skills that you possess.

Appreciate your help though KjBox, I really do

Unfortunately not KjBox, I just used that description as part of my sample data. The only constant or common denominator/pattern is the level I'm afraid.

The text with or without dots (periods) is as such: -

no dots = level 1
1 dot = level 2
2 dots = level 3
3 dots = level 4
etc

Your solution is perfect KjBox and aligned to what I wanted.

However, I guess my problem lies with some text having a period ( . ) or not and whether it needs to be joined to the rows above.

My lists of data are based on hierarchal equipment, a Level 1 would not want to be concatenated with the row(s) above it.

In version 2.2, rows 24 to 26 are correctly joined using your code, but if there is an equipment name below this (noted as Level 1 in the Hierarchy column). Example, if I add row 27 as a Level 1 equipment name, then it should not be concatenated with rows 24 to 26. I've tried to show this in version 2.3.

With the current coding it would concatenate to [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[TD="width: 507"]. . ALTERNATOR ASSEMBLY C/W MOUNTING BRACKET ENGINE ASSEMBLY #2[/TD]

[/TABLE]

Instead it needs to be
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[TD="width: 507"]. . ALTERNATOR ASSEMBLY C/W MOUNTING BRACKET [/TD]

ENGINE ASSEMBLY #2

[/TABLE]

The criteria being the hierarchy column taking precedence, if this make sense?

I'm probably not explaining myself too well, it confuses me no end at times...my apologies

P.S. For a PQ solution, I could only come up with being able to join the text starting with the " . " and the cell below it. Any more and it was beyond my intellect to solve LOL.

Wow KjBox, how stunning is that !! Thank you very very much. How does one be able to understand how to do it, let alone create a solution?

I have one slight problem though, if you could advise? Checking through my thousands of rows of data, there are points where the level hierarchy equipment could follow some equipment that start with a " . " (of which you have already concatenated) . In this instance the higher level equipment would be part of that join.

I've attached a revised document and I have put the hierarchy on another column. I have highlighted the dynamic range it would need to concatenate. I would assume the code could take into account what level it is, then perform the concatenation in between those levels?

See what you think and I really wouldn't want to put you out because you have been very helpful already.
Best
Paul

Hi KjBox, yes you are correct, apologies for not saying it correctly. Col A is my original table, and Col B is what I want to have as a final solution, and yes with no spaces.

I have attached a revised document with both columns separated.

Best
Paul

Gurus

In respect of my post for a Power Query solution https://www.ozgrid.com/forum/f…at-are-dynamically-spaced

If I fail to receive any solution I may have to resort to VBA. However, although I know a bit of VBA this is beyond my skillset.

I have attached a workbook example with an Equipment List. The names of the equipment in Col A "Equipment Description" can be on 1 row, 2 rows or even more (this is dynamic). Is there a VBA solution to combine whatever cells are required to formulate them into Col B "Concatenated Equipment Description".

Any solution would be gratefully received.

Best
Paul

Hi All, I'm very very new to Power Query and just love the possibilities with this system. However, I still lack the knowledge to be able to solve my problem.

I have set up a query to grab and transform a workbook which contains hundreds of Parts List tables. I have successfully transformed the data and loaded it into a worksheet but my problem is the Equipment Description can be on multiple rows, yet I want them to be merged in 1 row.

The Equipment Description below is my original data. As you can see below; Row 3 " CAT c7 w/ EMC " needs to be merged/joined/concatenated with Row 2 " . ENGINE, DIESEL, 374 HP " to equal " . ENGINE, DIESEL, 374 HP CAT C7 w/ EMC "

The Concatenated Equipment Description column is what I am trying to achieve, regardless if the Equipment Description is 1 row or split over 2 rows or more...
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[TD="width: 226"]Equipment Description[/TD]
[TD="width: 356"]Concatenated Equipment Description[/TD]
[TD="width: 233"]# of dynamic rows to concatenate[/TD]

ENGINE ASSEMBLY

ENGINE ASSEMBLY

0

. ENGINE, DIESEL, 374 HP

. ENGINE, DIESEL, 374 HP CAT C7 w/ EMC

CAT C7 w/ EMC

2

. . ENGINE MOUNTING

. . ENGINE MOUNTING

. . ENGINE FRONT

. . ENGINE FRONT ACCESSORIES MOUNTING BRACKET

ACCESSORIES

3

MOUNTING BRACKET

. . ENGINE OIL DIPSTICK

. . ENGINE OIL DIPSTICK AND FILL TUBE

AND FILL TUBE

2

. . ENGINE OIL FILTER

. . ENGINE OIL FILTER

. . ENGINE OIL COOLER

. . ENGINE OIL COOLER

. . ENGINE CRANKCASE

. . ENGINE CRANKCASE BREATHER CAP

BREATHER CAP

2

. . TURBOCHARGER OIL

. . TURBOCHARGER OIL LINES

LINES

2

. . EXHAUST MANIFOLD

. . EXHAUST MANIFOLD

. . ENGINE AIR INLET

. . ENGINE AIR INLET PRE-HEATER

PRE-HEATER

2

. . VALVE COVER

. . VALVE COVER ASSEMBLY

ASSEMBLY

2

FLYWHEEL

FLYWHEEL

. FLEXPLATE AND

. FLEXPLATE AND FLYWHEEL

FLYWHEEL

2

. . FAN ASSEMBLY C/W

. . FAN ASSEMBLY C/W BELT

BELT

2

. . ALTERNATOR

. . ALTERNATOR ASSEMBLY C/W MOUNTING BRACKET

ASSEMBLY C/W

MOUNTING BRACKET

3

[/TABLE]

I have tried multiple If Conditional Columns, I have tried merging columns, transposing columns, grouping (not very successfully) and have spent days and days searching the net to find a solution.

As I am a basic novice, maybe there is something I'm totally missing but wondered if any gurus here would know how it can be solved?

More than willing to attach any workbook examples if required?

Apologies to the moderators if I have failed to comply with any rules regards inserting tables etc.

Thank you
Paul

Wow, thank you so much Carim. I had used the Countif, but only once instance.

You are a star

I'm wracking my brain over this one and I'm sure it is sooooo simple but I have brain freeze.

I have attached a simple spreadsheet with two projects (Project A and Project B). They show tasks that are mandatory (yes) / not mandatory (no) with a completion status (RAG) ranging from Blue (completed) to other colours (not completed).

If a task is mandatory then the task is only completed when the RAG status is Blue, otherwise it is not completed if it a.n.other colour is selected. If the task is not mandatory then it needs to be ignored in the calculation.

Once all mandatory tasks are completed then the result needs to denote this accordingly in the 'All tasks Complete' cell.

I'm struggling to find a solution with a formula, is there anyone that can advise?

Best
Paspuggie48

I am a basic VBA coder but lost as a bat on this one. I have successfully coded many macros to open IE and navigate through multiple pages, drop-down menus and run reports. However, one I cannot fathom is a sub-menu list option, which is a hover over / mouseover list.

On the main page is the sub-menu called "Search". When I hover over this Search button with my mouse there is a list of options and in this list is "Modification Register". If I select Modification Register with my mouse, it then navigates to that page. How do I do that with code? I have tried various options in my coding, such as getElementbyID, FireEvent "onchange" but nothing works.

Here is the HTML code:

<tr>
<td rowSpan=”1” colSpan=”1”>
               <div class=”"main menu"” id=”"menuTarget"” style=”"HEIGHT: auto; DISPLAY: block"”>
                              <ul class=”"mainMenuList"” id=”"navmenu"”>
                                             <li>
                                                   <a class=”"Selected"” href="”https//website address#”" shape=""””>..</a>
                                                   <ul class="”subMenuList”">
                                                   <li>
                                                         <a class=”"Enabled"” href=”"https//website address#"” shape="">Modification Register</a>
                                                         <img style=”"HEIGHT: 1px; WIDTH: 1px”" alt="" scr="”website address.gif”"></img>

Unfortunately I cannot provide the website address as it is sensitive information. I appreciate this may cause a barrier or no solution but this is my last option to ask.

Can anyone suggest how I can do this?

Paspuggie

Re: Assign Macro Shapes to email specific files

I found a small bit of code to determine the Shape name variable on the active shape clicked, then use that variable to find in the range.

Sub Mail_Attachment()


    Dim OutApp As Object
    Dim OutMail As Object


'Get Name of Shape that initiated this macro
  ButtonName = Application.Caller


'Find Name of Shape in range
  Set Rng = Range("H2:H5")
    With Rng
        .Find(What:=ButtonName, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows).Activate
    End With


    'Create email
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    On Error Resume Next
    With OutMail
        .To = ActiveCell.Offset(0, 3).Value 'email address 4th column along
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Please find attached your document"
        .Attachments.Add (ActiveCell.Offset(0, 1).Value) 'path of filename in next column
        .Display
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Seems to work

Re: Assign Macro Shapes to email specific files

Thanks again Carim. I can physically select the cell and run the macro to send an email. That's not a problem, but what I need is for it to happen by clicking the Shape (the Shape has the macro assigned to it).

Eg if Shape "1" is clicked, it needs to find "1" in the table, attach the document from "C:\Personal\EXCEL\Add Macros to Shapes1.xlsx" and send to "Paul" with "Paul's_email_address.com"

Eg if Shape "2" is clicked, it needs to find "2" in the table, attach the document from "C:\Personal\EXCEL\Add Macros to Shapes2.xlsx" and send to "David" with "David's_email_address.com"

etc

Re: Assign Macro Shapes to email specific files

Thank you carim.

I'm ignorant and apologise, but how do I code it so it finds the right row? eg once I click on Shape 1, for it to find the number '1' in Col H ?

I've had no formal training for VBA, so I'm at my limit of know-how.

I have multiple shapes, numbered sequentially from a list of integer numbers in a table. The table also contains data of location of filename, name of a person and their email address.

Attached document is here forum.ozgrid.com/index.php?attachment/73282/

The code currently assigned to Shape Number "1" is below. (most of my email code is taken from 'rondebruin )

Sub Mail_Attachment()


    Dim OutApp As Object
    Dim OutMail As Object
    Dim myRange As Range
    
    Set myRange = Range("I2")




    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)




    On Error Resume Next
   ' Change the mail address and subject in the macro before you run it.
    With OutMail
        .To = Range("K2").Value
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Please find attached your document"
        .Attachments.Add (myRange.Value)
        .Display
    End With
    On Error GoTo 0




    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

I don't want to apply a macro to each shape, changing the code to suit (that seems inefficient). So, how do I modify the code so that each shape will automatically know which data to find in the table. Maybe using a index/match formula or find or...I'm not sure ????

Some advice would be greatly appreciated.
Cheers

Re: Date between range and reference adjacent Month

Think I found a solution?

eg in Cell G4

=LOOKUP(2,1/($B$2:$B$13<=G4)/($C$2:$C$13>=G4),$A$2:$A$13)

If anyone knows if there is anything better, please let me know.

Dear Gurus

I have brain freeze today and struggling with a formula. I have a range of start and end dates of each month, which are referenced by a Month number.

I've attached a simple version of my workbook.

forum.ozgrid.com/index.php?attachment/72671/

Col A is the Month No., Col B is the month start date and Col C is the month end date. I've put some examples of what I'm looking for to the right hand side. If someone puts a date in a cell, then I need the adjacent cell to denote what month number it is.

Would someone have advice which formula to use?

Cheers
Paul

Re: Set print area to cell reference

I ended up with this...

'find the last table and cell used in Col B. Offset to right hand-side to find bottom right of print area
Set lastCell = Cells(Rows.Count, 2).End(xlUp).Offset(1, 23)


'print from cell A1 to that bottom right cell
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address

Again, I'm new to actually trying to write code, so don't know if it is correct coding, but it works for me

Re: Set print area to cell reference

Let me work on that synopsis then Carim

Re: Set print area to cell reference

I tried Carim, but as you know from the previous post the amount of tables is dynamic.

For 1 table it would be : -

ActiveSheet.PageSetup.PrintArea = "$A$1:$Z$26"

I just don't know enough about coding to adjust it to suit the range, depending on how many tables are inserted...I'm still learning :jumpupdo: