User Profile
Serdet
Copper Contributor
Joined Apr 26, 2021
User Widgets
Recent Discussions
- 1.4KViews0likes0Comments
Export data transformation
Hello, I have a system that exports data in a poor fashion and I need help transforming this into readable data. Standard export Date Name Total Hours A Hours B Hours C Hours D Hours 01/01/2023 John 10 2.5 2.5 2.5 2.5 01/01/2023 Maggie 20 5 5 5 5 Desired output Date Name Hours 01/01/2023 John 2.5 01/01/2023 John 2.5 01/01/2023 John 2.5 01/01/2023 John 2.5 01/01/2023 Maggie 5 01/01/2023 Maggie 5 01/01/2023 Maggie 5 01/01/2023 Maggie 5 For every A - D hours that has value greater than 0, it would need to create a new row and show this as hours for each available category. Things to note - there will only ever be A - D hours, some hours in A - D will be 0/blank Is this possible to solve? Ideally without a macro where the source table is pasted and another sheet in the same workbook shows the desired outputSolved1.6KViews0likes4CommentsRe: Pulling table data from many sheets into one table
mathetes Patrick2788 Thank you very much for helping me with my problem. I have revised the formula to pick up "3m" in column B by using the below: =LET(stack,VSTACK('1:20'!$B$14:$AB$250),FILTER(CHOOSECOLS(stack,1,3,25),TAKE(stack,,1)="3m"))1.4KViews1like0CommentsRe: Pulling table data from many sheets into one table
mathetes Thank you for your quick response. As data is sensitive, I have created an example document and shared in the below link. Sheets 1 - 20 are in the same format as the real document. I have also added a 'Required Output' sheet which shows how the data needs to be extracted and formatted. Your help would be much appreciated 🙂 https://docs.google.com/spreadsheets/d/1n5tsI966LF2wJepWRqCjnzIMWq3N6dB1/edit?usp=sharing&ouid=102728026850648187467&rtpof=true&sd=true1.5KViews0likes4CommentsPulling table data from many sheets into one table
Hi Everyone, I have an excel workbook containing 20 sheets (named 1 through to 20). Each one of these sheets contains data in columns B - AB and rows 14 - 250. I want to pull columns D & AB from every sheet into a new summary sheet if column B contains text "3m". Is this possible? Especially without getting loads of row gaps in the data. The formula will need to be present so when data is entered in sheets 1 - 20, it automatically pulls through to the summary sheet. Many thanks, Elliot 🙂Solved1.7KViews0likes6CommentsRe: Excel Planner
Hi mtarler Thank you for taking the time to help me with my problem. Is there anyway to transpose other data fields in with the ID's? For example, I have another column titled 'removal or install' which I would like the data to be tagged with the ID. Many thanks, Elliot1.2KViews0likes1CommentRe: Excel Planner
Hi, Macro is fine within my organisation. I will still need to keep the original format but have an altered view for other people within the organisation. For example, the planner will require the orignal view. The people following the plan will require the new proposed view. Cheers. Elliot1.3KViews0likes3CommentsExcel Planner
Hi All, I have an excel spreadsheet which is used as a tracker as is laid out as below ID PLANNED START DATE 1 01/01/2021 2 02/01/2021 3 02/01/2021 4 04/01/2021 5 05/01/2021 6 06/01/2021 To make the table for visually appealling and easier to use, is there a way to automate this in a different view to read like the below 01/01/2021 - Friday ID 1 02/01/2021 ID 2 ID 3 'and so on' This will make the users be able to easily see how many ID are due on each day in a simple clear format. Many thanks, Elliot1.4KViews0likes5CommentsRe: Multiple IF formula calucation
Hi Riny_van_Eekelen, Thank you for sending that over. I have copied your formula into my spreadsheet but are receiving a #NAME? error. I have double-checked the two but cannot spot any differences. Any ideas on where I might be going wrong? Many thanks, Elliot4.1KViews0likes5CommentsRe: Multiple IF formula calucation
Hi Riny_van_Eekelen Thanks for getting back to me. I have tried to implement the formular used in your spreadsheet but are having problems due to my 'dataset2' being located on a different 'Tonnage' tab. Please see the attached. Many thanks, Elliot4KViews0likes8CommentsMultiple IF formula calucation
Hi All, I have a 2 datasets similar to the below. Data Set 1 Pressure Plume Size Flowrate (kg/hr) Flowrate (T/hr) Save Value (k$/yr) 3 0.5 3 1 3 2 3 3 3 4 Data Set 2 Pressure Plume Size Flowrate (kg/hr) Flowrate (T/hr) Save Value (k$/yr) 3 0.5 5.2647 0.00526 0.59954 3 1 7.89706 0.0079 0.89932 3 2 33.612 0.03361 3.82773 3 3 93.2505 0.09325 10.6194 3 4 147.056 0.14706 16.7467 3 5 215.417 0.21542 24.5317 I will be entering data into dataset 1 (Pressure & Plume Size). I want to use dataset 2 as a look up table so if the pressure and plume size match a line in dataset 2 the other columns need to auto populate according to the match. Any suggestions would be much appreciated. Many thanks, ElliotSolved4.7KViews0likes12CommentsRe: Multiple Condition IF Statement
SergeiBaklan Hi Sergei, I have now added a new column which has to come into the equation and was wondering if you could give me a helping hand. A new column called 'On Hold' has been added to the end of the table (let's say column Z). If fields within this column state 'On Hold' I need the previous 'Good to Go' column to state "N/A". I hope this is all clear. Cheers Elliot1.6KViews0likes3CommentsRe: Multiple Condition IF Statement
SergeiBaklan Hi Sergei Baklan, Thanks for your quick reply. I have tried this formula in my spreadsheet and it is not working, when I change cells to blank it does not change the 'Good to Go' column value. I have tried copy & pasting my data over to your spreadsheet and it seems to work perfectly. Columns A - D are all populated by IF formulas, I'm not sure if this has anything to do with it not working. Please see the attached spreadsheet as this may give you an idea as to why it is not working (Data is actually located in columns AF:AI) Kind regards, Elliot1.8KViews0likes8CommentsMultiple Condition IF Statement
Hi All, I have a dataset similar to the below; Mech Complete? Scaffold Erected Insulation Removed WOL Raised Good to Go? Yes N/A Yes N/A Yes Yes N/A N/A N/A Yes N/A N/A Yes I need a formula column E ('Good to Go') to perform the following logic; If cells in columns A - D contain "Yes" or "N/A" formulate to "Yes" If cells in columns A - D contain a single 'Blank Cell' formulate to 'Blank' Any help will be much appreciated! ElliotSolved2.1KViews0likes10Comments
Recent Blog Articles
No content to show