# Posts by alansidman

• ## public (?) function to make a full name

You may not need VBA. Depending upon which version of Excel you are using, FlashFill may be an option available to you.

• ## Using Countif to calculate totals from another excel tab

In M6 =COUNTIFS('List of Tickets'!\$M\$2:\$M\$199,Sheet1!L6) and copy down.

• ## Calculate final sales price for a desired profit

A sample worksheet would help to get a solution.

• ## Help with creating VBA to clean up and organize some data

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 a video which demonstrates how to use Power Query code provided.

• ## Help with creating VBA to clean up and organize some data

Can be achieved with Power Query.

Code
1. let
2. Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
4. #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 8), type number),
5. #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-US")[Modulo]), "Modulo", "Column1"),
6. #"Filled Up" = Table.FillUp(#"Pivoted Column",{"1", "2", "3", "4", "5", "6", "7"}),
7. #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([0] = "Call")),
8. #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
9. in
10. #"Removed Columns"

• Book2.xlsm

• ## Calculate Total Times Ticket Open

In the PQ solution, if you add new data, click on the Refresh All and your PQ will update automatically

• ## Calculate Total Times Ticket Open

Can be accomplished with Power Query/Get and Transform Data

File attached for review. Note that you have approx 40 rows with no reporting date. How would you like those handled?

## Files

• 12803940.xlsx

• ## Conditional Formatting

Here is a UDF developed by Jindon that allows you to do Multi lookups

I have put the code into the attached

• ## Transpose Data from Rows to Columns

An alternative solution that requires no coding is with Power Query

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 a video which demonstrates how to use Power Query code provided.

• ## Autofilter and Criteria Help Vba

Please provide a file that matches up with the code so that it can be tested effectively and determined the cause of your issue. Without the file, it is difficult to determine the cause.

• ## VBA script to extract numbers and place in two separate columns

Here is the PQ for your updated file.

Code
1. let
2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
3. #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Column1], "eFine")),
4. #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
5. #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"Column1.2", "Column1.4"})
6. in
7. #"Removed Other Columns"

• ## VBA script to extract numbers and place in two separate columns

Suggest you show us a realistic representation of your actual data if that was not the case in the original post. I cannot distinguish the issue from what you have posted. Show us again a realistic before and after for all types of scenarios.

• ## VBA script to extract numbers and place in two separate columns

Power Query solution

Code
1. let
2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
3. #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] = "eFine: 2404589 eFood: 12015" or [Column1] = "eFine: 2404852 eFood: 12827" or [Column1] = "eFine: 2404860 eFood: 12914" or [Column1] = "eFine: 2404892 eFood: 12855")),
4. #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
5. #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"Column1.2", "Column1.4"}),
6. #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Column1.2", "Test1"}, {"Column1.4", "AMP"}})
7. in
8. #"Renamed Columns"

• test1.xlsx

• ## VBA - If Cell is not blank, then copy / paste cell to range in same row

Patience is important in these types of forums. We are all volunteers that have lives. We work, we sleep, we play and sometimes we participate in these forums. Additionally, this is a world wide forum. We are from many countries and regions. While you may be at work, others may be asleep. Do not bump your thread until at least 24 hours have past. If your need is urgent, you may consider hiring a consultant instead of using this "FREE" forum. Just be patient.

• ## Match date in cell to the same date in a range and return value from cell below

In order for you to extract the data you wish, you need to have your data in unique columns. You currently have data and dates in the same columns and mixed with the rows. You will need to either normalize your data or write some advanced VBA. Personally, I would prefer to see the data in the form of

Title--Date--Amount--Change

Four columns then allow for lots of analysis including Pivot Tables, Look ups, summarizations, etc.

• ## Excell calculations

Post a sample workbook that we can manipulate. Keep it to 8-10 records and show us a manually mocked up solution also.

• ## Show Age and Age Range from Date of Birth entered into userform

I have added code tags to your post as required in the forum rules you agreed to when you joined. Please take a few minutes and read the rules again and abide by them in the future.

• ## Run-time error 1004 Pastespecial method of worksheet class failed

I have added code tags to your post. Please take a minute or two to re-read the forum rules you agreed to when you joined. There are not many and we take them seriously here.

• ## How do you create a formula whose output is a BLANK cell?

Please explain in simple English what you are trying to prove because your formula does not make sense to me.