Results for "multilevel tree data"

Good news.

If everything is ok, close the thread by clicking

What I suggest is :

• Build a new tab called "Tables" in which we will find the valid terms depending to the choices made in tab "Navigation". Theses choices are determined by a formula at each step as for instance :

=sort(unique(query(Data!A:G,"select G where A='" & Navigation!A2 & "' AND B='" & Navigation!B2 & "' AND C='" & Navigation!C2 & "' AND D='" & Navigation!D2 & "' AND E='" & Navigation!E2 & "' AND F='" & Navigation!F2 & "' ",0)))

• Build a new tab called "Navigation" in which you can find data validation in cells row#2
• If a change of one choice has been done, I erase all the choices made on the right by this script

function onEdit(event){
  var feuille = event.source.getActiveSheet();
  var cellule = event.source.getActiveRange();
  if (feuille.getName()=='Navigation' && cellule.getRow()==2){

    feuille.getRange(2,cellule.getColumn()+1,1,7).clearContent()
  }
}

in A6 of "Navigation"

=IFERROR(sort(unique(query(Data!A:H,"select H where A='" & Navigation!A2 & "' AND B='" & Navigation!B2 & "' AND C='" & Navigation!C2 & "' AND D='" & Navigation!D2 & "' AND E='" & Navigation!E2 & "' AND F='" & Navigation!F2 & "' AND G='" & Navigation!G2 & "' ",0))))

Hope that is clear and fit to your request. Feel free to give me your feedback.

ok, now I will try to understand your project ! (may be this evening or tomorrow morning)

No problem Scilla, welcome in sheets-pratique.

However, access to your spreadsheet is denied. Please share as follows https://www.sheets-pratique.com/fr/cours/partage (you may be able to find help in English on the web).

Done; thanks again

This is great! Many thanks Mikhail

Many thanks; I think this new link should work:

https://docs.google.com/spreadsheets/d/11j4YoKNRkSSyigvhXUS1zZcg6DhT4FRrjF0ZhbssW2M/edit?usp=sharing

I will edit the initial post with this new link as well.

Hi,

I created a google sheet with the data from a multilevel tree and looking for ways to easily navigate/ access the data.

Data structure : my current tree has 8 levels (1 level per column); the first 7 levels are 'objectives' and some 'solutions' are displayed at level 8; for instance: Level 1 objective is to 'Increase profitability', to achieve this objective there are various objectives at level 2 like 'Reduce operating costs' or 'Increase profitable business', then these level 2 objectives are sub-divided into level 3 objectives etc. until we reach a number of 'solutions' at level 8.

Also, each solution is categorized e.g. 'Process' solution, 'Technology' solution etc.

Finally, the solutions can be applicable to multiple objectives e.g. solutions A, B and C can be applicable for objective X (which is a level 4 objective), AND also applicable for objective Y (which is for instance a level 6 objective); when I met this type of situation, I did not copy the 'solutions' for all applicable objectives as I want to minimize the number of rows and cannot maintain multiple lists of identical solutions as this would be very difficult to manage over time; as a workaround, I only indicated 'See: Objective X' as a 'solution' when needed.

At this stage, I managed to create a pivot table which displays the different objectives in rows, the categories of 'solutions' as columns and the unique 'solutions' as 'values'; unfortunately, this is difficult to read / use as there are many levels, objectives and solutions.

I tried using 'filters' and'slicers' but this is still not user friendly; I'm looking for a better solution like a basic form maybe where one could select an 'objective' (at level 1, 2,...7), see what sub-objectives are available (if any) and ultimately see all the solutions (by category) which are available to achieve the selected objective(s).

Ideally, this form could also show to what upstream objectives a selected objective (or solution) is contributing to, but this is more a secondary goal for me; like the cherry on the cake ;)

I considered using some kind of relationship database but I do not have the skillset to do that and thought there might be an easier solution directly in Google sheet.

Sorry for the long post; just wanted to be clear - I would appreciate any help; you can find an extract of my data sheet and draft pivot here:

https://docs.google.com/spreadsheets/d/11j4YoKNRkSSyigvhXUS1zZcg6DhT4FRrjF0ZhbssW2M/edit?usp=sharing

Thanks;

Syl

Excactly what I needed, thank you very much !

Hello,

You may use Power Query.

1. Format your input table as Table (Ctrl+T when focus is on a cell from the table)
2. Then, position the cursor on the Excel table, Select Data > Get & Transform Data > From Table/Range.
   Excel opens the Power Query Editor with your data displayed in a preview pane.
3. In the Power Query editor, Select "ContactType" column, then Transform > Pivot Column.
   In the Pivot Column dialog box, in the Values Column list, select Value.
   Select Advanced options, and then select an Aggregate Value Function. In this case, select Don't aggregate
4. OK

Load the data in the sheet.

That's all.

PS : If you add, or change data in your input table, go to the result table and hit Data > Refresh

I think I get what you're aiming for with the macro. So, when you open the file and select a cell like A7, you want the macro to grab certain cells from that row (like A7, C7, E7, G7) and paste them into a new workbook, right?

Hello and welcome to the forum

Try with the code herein

Sub Macro1()
Dim newwb As Workbook
Dim r As Integer
Dim TS As ListObject

Set TS = ThisWorkbook.ActiveSheet.ListObjects(1)

On Error Resume Next
r = TS.ListRows(ActiveCell).Range.Row - TS.HeaderRowRange.Row
If r = 0 Then MsgBox "No cell selected in table !", vbCritical, "Wrong selection": Exit Sub 'if selected cell in not in table range
On Error GoTo 0

Set newwb = Workbooks.Add
With ActiveWorkbook.ActiveSheet
    .Range("A1") = TS.DataBodyRange(r, 1).Value
    .Range("A2") = TS.DataBodyRange(r, 3).Value
    .Range("A3") = TS.DataBodyRange(r, 5).Value
    .Range("A4") = TS.DataBodyRange(r, 7).Value
End With
End Sub

rem : Please think to update the reference of your excel version in your account as "English" does not help. Use excel 2008, 2016,;... or if you are a MacUser --> MAC2011, MAC20xx

Sincerely

I would like the Macro to do the following:

When I open the file and select, for example, A7

Then I run the macro

In the macro, I have a setting that if I first selected a cell from row 7, then the macro places the 1st, 3rd, 5th and 7th cells from row 7 in a new workbook in the following way

1. in A1

3. in A2

5. in A3

7. in A4

Also, I would like to choose the save location

Good evening corriedtoppin,

Would a scale like this one be better for what you want to do?

Hi,

I've imported a 50000 lines CSV file with customer contact informations, but I would like to rearrange data.

My input table has this format :

Some customers has email and phone contact, but some has only email, or only phone contact. If the customer has email and phone contact, I have 2 lines with the same customer ID

And I would like to convert my table to have unique customer IDs, like this :

I've no idea how I can do this, I tried a lot of thing, with pivot tables, xlookup, but no success.

I don't even know if it's possible with simple formulas, or if I need to use VBA ?

If someone has any clue about how to do this, I would be very grateful :-)

Thanks a lot

Olivier

Let me start over.

Good day excel world.

I have a concern.Below link have a list of countries that carries each country unique data.

Consumer Confidence - Countries - List

Now let's take the 1st two example:

#1 Country: Albania Data: -13.60
Below is Albania historical values 
snapshot:https://imgur.com/C3GjVvBIf 
you notice all there historical data are measure with a (-) number.
https://tradingeconomics.com/albania/consumer-confidence
Albania Consumer Confidence

and

#2 Country: Argentina Data: 42.40
Below is Argentina Historical values 
snapshot:https://imgur.com/ECIbh1j
If you notice all there historical data are measure with a positive number.
https://tradingeconomics.com/argentina/consumer-confidence
Argentina Consumer Confidence

My problem is this:

How can I use 3-Color Scale "Heat map", using conditional formatting, to show it correct color code for each country?

Below is the WRONG EXAMPLE of conditional formatting use

directly.

What do you suggest I do?

Hello Excel World.

I'm in the process of creating a 3-Color Scale "Heat map", using conditional formatting for investment purposes.

But I am having some challenges in creating the heat map, because each data carries there own unique values and may differ extremely from each other.

See Link below, of countries listings and the values outline under the header "LAST"

https://tradingeconomics.com/country-list/consumer-confidence

I also thought with myself. If the heat map should work effectively, each value or each cell reference should have there own heat map values.

But what is the effective way in creating these values or heat map ratio.

See link below of one of the example given.

https://tradingeconomics.com/albania/consumer-confidence

In conclusion.

What is the best direction to create this?

What are your experiences if any?

How do you want me to proceed?

Thanks in advance

corriedtoppin a écrit :

Sub Test1()

Workbooks.Open (Filename: = "C:\Users\Corey\Desktop\Anton Heatwave\PMI\Countries Indicators #1 NSB(1).xlsx") 'Filename to adapt

End Sub

Still nothing

Hello,

That one look like a valid full name. If that's not working anymore, I can't help you.

Sub Test1()

Workbooks.Open (Filename: = "C:\Users\Corey\Desktop\Anton Heatwave\PMI\Countries Indicators #1 NSB(1).xlsx") 'Filename to adapt

End Sub

Still nothing

I don't know. That's the name of the excel file: Countries Indicators #1 NSB (1)

Sub Test1()

Workbooks.Open(Filename:="C:\Users\Corey\Desktop\Anton Heatwave\PMI") 'Countries Indicators #1 NSB (1).xlsx

End Sub

I am only getting and error.