Please explain in simple English what you are trying to prove because your formula does not make sense to me.
Posts by alansidman
-
-
Blank templates with no data to work with is not helpful. Suggest you resubmit with sample data. We do not need hundreds of records. Smaller samples that are representative of your actual data is what is needed.
-
I have added code tags for you in accordance with our forum rules. Please take a few minutes to read those rules which you agreed to when you joined. In the future, please abide by those rules.
Alan
-
Attach a sample worksheet to allow us to manipulate data and test our solutions.
-
Hopefully, someone with some advanced VBA skills will come to your rescue.
-
Look at this video on how to achieve with Power Query which is part of Excel.
-
There seems to be no pattern here. Can you provide some type of criteria that would allow us to develop a solution.
-
Macros allow you to write program scripts that automate the work you would normally do manually. The advantage is when you have a lot of calculations that would take lots of time to do manually, macros can do in seconds or minutes depending how large or complex.
-
Ok. Not my forte. Will leave this for those who use advanced formulas. Good Luck.
-
Using Power Query, here is the Mcode to solve
Code- let
- Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
- #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Day", type text}, {"Area", type text}, {"Product", type text}, {"SalesmanName", type text}}),
- #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "SalesmanName"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
- in
- #"Grouped Rows"
Attached is a file you can follow the steps.
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided. -
Used Power Query to develop random selections by class. Look at the attached file to see the actions taken.
-
Bring your data into the Power Query Editor. Highlight the first column. Select Transform on the Menu. Select Unpivot other Data. Select Home and Close and Load.
-
With Power Query, I was able to create the option for you to select the criteria and have the data updated. the file is attached for your review. Select your options from the drop downs and then click on the Command Button.
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided. -
Have you attempted any scraping with Power Query?
-
An alternative is to use Power Query. Here is the Mcode and I have attached the file for your review.
Display MoreCode- let
- Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
- #"Changed Type" = Table.TransformColumnTypes(Source,{{"No.", Int64.Type}, {"Zip Code", Int64.Type}, {"TIV", type number}, {"Adj", type number}, {"Column1", Int64.Type}, {"Cat Zone", type any}, {"V Lookup", type text}, {"State", type text}}),
- #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([State] = "CA")),
- #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Adj", Order.Descending}}),
- #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
- #"Filtered Rows1" = Table.SelectRows(#"Added Index", each ([Index] = 1 or [Index] = 2 or [Index] = 3 or [Index] = 4 or [Index] = 5 or [Index] = 6 or [Index] = 7 or [Index] = 8 or [Index] = 9 or [Index] = 10)),
- #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Zip Code", "TIV", "V Lookup"}),
- #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"V Lookup", "Zip Code", "TIV"})
- in
- #"Reordered Columns"
-
Here is a link to a article on how this works. I found this about a year ago and have used it many many times. This will be easier to follow than for me to regurgitate.
Alan
-
thanks for pointing out that the file was attached. In my attachment, I have recreated the expected results using Power Pivot. I created a measure using the concatenatex function. The measure is
=CONCATENATEX(Table1,Table1[Source],", ")
-
An alternative solution is a simple unpivot using Power Query/Get and Transform Data
-
How about a small sample for us to work with? No pictures! Upload a sample file.
-