Search results

Hi @Excel Wizard , Thank you for the quick and sharp function to solve my issue. However, when am extending the data range from 9 to the dataset i have the results appear as 0, would you know why? Regards, A!

Hi team, I have been trying to solve the below but for some reason, I cannot find a way...any help would be of great help. Attached is a sample workbook for reference. The workbook has the following, the first table has products with a combination of products, these are combinations that are...

Hi, I have used PowerQuery to unpivot columns and grouped the row; loaded it as a data model to insert as a pivot in a new worksheet. Just click on Data tab "Refresh all" to extract information. P.S. Power Query feature is built in office 2016 dont have to download it but you can download...

Sub Mail_Single_SendRequest() 'Working in 2000-2010 Dim wb1 As Workbook Dim wb2 As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim OutApp As Object Dim OutMail As Object Dim r As Integer, x As Double For r = 2 To 2...

Hi Pavan, Just have it enabled from File > options > add-ins > com Addins > Check on PowerPivot that should have it enabled for you. Kind regards, Anand

Hi Sam, It would be much easier if you can upload a sample workbook. Kind regards, A!

Hi Jas, I would use something like this. Update email address and details accordingly Sub Mail_Single_SendRequest() 'Working in 2000-2010 Dim wb1 As Workbook Dim wb2 As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim OutApp...

Once the data model is established and relationships are created amongst data model, you can use the function RELATED in Power Pivot which pretty much works like Index and Match / Vlookup. You can also lookupvalue to lookup information. Kind regards, A!

Hi Pavan, Attached sheet does list out information as required. Please note, in order to create relationship amongst data set you always have to data which are many to one kind of relationship. When I saw many I mean the data set which are transactional and one which is unique then you can...

Hi, You can add the table to power pivot and create either a measure or kpi to list values as required. Kind regards, A!

Hi Vsk, From the explanation above, I don't think you need a macro to do this trick for you. All you need is power query to append all the source values (considering all the headers of the source are same like in the example). once done create a pivot to list values or you can go one step ahead...

Hi Ratish, You can have it refreshed with a schedule refresh using Power BI. However, you might need a user license for the same. However, you can write a small macro to refresh query for you at a specific time and include a task in the windows task scheduler so that the file can open, refresh...

Hi David and Bosco, I found a easier solution with PowerQuery by creating a duplicate copy of certain fields and later unpivoting other columns to arrive at a solution. Thank you once again for looking up for me on the same. Kind regards, A!

Hi Ratish, If you're using Office 2013 then download the add-in Powerquery from url as mentioned below: https://www.microsoft.com/en-gb/download/details.aspx?id=39379 Choose version 32/64bit depending on your office suite. if you do not have administration access to install the add-in ask...

Thank you Bosco, David for helping me out here. I will see what needs to be done here to have the output as desired. Thank you once again...A!

Hi Ratish, Why not use Power Query to extract data instead of writing macros? It can extract information from various sources including the workbooks stored in a specific placeholder so long as you've access to the file. Kind regards, Anand

Hi David, Columns A through D are more like transactional data where the data keeps updating. The 3 worksheets are basically different sources put together in a spreadsheet. The Columns E and F are the binding factor for all the data they will remain static (e.g., no other options other than...

Hi team, I'm trying to retrieve header information with list of all unique values in columns from different worksheets. Attached is a sample sheet with the desired output, i'm not sure if index and match can be used to create as listed in the output sheet. In the output sheet lists the...

Hi John, Thank you once again...it works like a magic. Wish you nice day ahead Kind regards, A!

Thank you for the quick solution and reply John. Works like a charm. Surely, it does help in fixing my solution. I was wondering if there is a way to do this without a VBA script just by using excel functions. Kind regards, A!

Hi team, I'm trying to do the following. without delimiting the text string with comma as separators, I would like to count the length of characters in the below string and add 0 after AD if the number of characters is 6 and if 7 I would like to leave it as is. Having said this, the occurence...

Thank you for the quick response, am trying to list the possible options using data validation if text matches in a list of column. Having true/false will not work. I can use combo box to do this but i have to place as many as 100 of them. so was thinking if there is a easier way to do it using...

Hi, Am trying to use both search and lookup function in data validation but not able to use it. Any suggestions to use other combinations which can help me search a text and lookup for possible options to list values? =IFERROR(LOOKUP(2;1/SEARCH(C3;a1:a20);b1:b20);"") Thanks in advance. Kind...

Once the file is created, it will take only that timeframe (worst case scenario less than a minute) as the source is going to be in the local pc of the user.

Hi Jeremy, This was basically because of the formatting issue. I cleared the format and applied another time format to make it working for you. Hope this helps :) Kind regards, A!