|
Old James posted:
This is loving brilliant. If 'Column B' does have four values to concatenate to 'Column A', then the 3's in the above code should be 4's. Just copy the code and paste it into cell C1, assuming data starts in A1 and B1.
|
#
¿
Mar 20, 2013 19:43
|
|
|
|
| # ¿ Apr 27, 2024 23:42 |
|
|
http://stackoverflow.com/questions/8164867/excel-macro-formula-adding-quotes-around-formular-causing-vlookup-to-not-work TL;DR - You cannot mix RC and A1 style references in the same formula.
|
|
#
¿
Sep 4, 2014 22:23
|
|
|
Yeah I had to refine the search quite a few times before anything close turned up. Sometimes it is just dumb luck!
|
|
#
¿
Sep 5, 2014 00:30
|
|
|
Vegetable posted:Yeah, that's what I thought to do, but given that this is for a class, I was wondering if there's some lesson I'm supposed to be applying. A quick Google Search didn't give me much (and I'm not sure what to look for). That sounds like a decent 'lesson' itself - you need to error-proof or otherwise filter your inputs to avoid bad data. Garbage in = Garbage out! Anyways, what part of this are you allowed to modify or what part did you create? Can't you just ignore zero-unit bids, look for the lowest bid that is greater than zero?
|
|
#
¿
Mar 4, 2016 01:08
|
|
|
You can use indirect inside the sum formula: '=sum(indirect("A2:A"&B9)) , where B9 is your value of the last row. You're basically creating the range of cells in string form so you can append the last number/row from your specified cell.
|
|
#
¿
Dec 6, 2016 21:24
|
|
|
Haven't had my caffeine yet but convert each sheet into a named table and merge them using Power Query into a single table which you 'Load To' a new sheet?
|
|
#
¿
Sep 9, 2017 16:23
|
|
|
Switch to INDEX(MATCH()) instead of VLOOKUP. The last argument of MATCH() is a -1,0,1 value that specifies how the lookup value is compared in the lookup array; a value of -1 will find the smallest value that is greater than or equal to the lookup.
|
|
#
¿
Mar 22, 2019 16:28
|
|
|
Can you just format the line series and turn off line color, leaving only marker color enabled?
|
|
#
¿
Dec 16, 2019 15:31
|
|
|
Harminoff posted:Does anyone know of a way to filter data by wildcard in power query? Could you check for 'starts with Filter' and 'ends with out' instead? Can't seem to find docs on power query wildcards but it doesn't look as easy as * or %.
|
|
#
¿
Feb 7, 2020 04:38
|
|
|
Dependent dropdowns? https://exceljet.net/dependent-dropdown-lists
|
|
#
¿
Nov 21, 2020 20:49
|
|
|
C-Euro posted:I'm trying to figure out if there's a way to highlight a cell or row based on the contents of two cells in the same row, relative to each other. I have a spreadsheet where one column is a Category number (1,2,3 etc.) and another column is a Value (0~200 let's say). I want to write a rule or function that says for example "For each row, if Category = 1 and Value is between X and Y, highlight that row (or some assigned cell within that row)", and so on for Category = 2 or 3 or further up. Is that a thing in Excel? You can do that with conditional formatting. Are you going to have the X and Y values hardcoded or will you want to be able to adjust them? An example where the X and Y values are taken from cells on the sheet:
|
|
#
¿
Sep 27, 2021 18:21
|
|
|
Rabbit Hill posted:I have a big spreadsheet which was set up in a clumsy manner, the biggest problem being that it combines two variables in single columns. I want to separate these columns of combined variables into separate columns. That can be done with an unpivot and then column split in the Power Query editor if you are willing to lose the cell/color formatting. There are other benefits to this method like being able to go back and edit the steps but I won't preach my Business Intelligence Bull poo poo here. You may have to download the PQ add-on from Microsoft if you don't have it installed. 1. Select the entire range of data then go to the Power Query tab and click 'From Table/Range', it should open the PQ editor. Open the Transform tab, click the Year column header, then click the dropdown next to Unpivot Columns and click Unpivot Other Columns. You should then have 3 columns titled Year, Attribute, and Value which correspond to your Year, Agency Crime, and Count columns. 2. Click the Attribute column, click the Extract button, then the Text After Delimiter dropdown option. Enter the hyphen as the delimiter and click OK, it should split that Attribute column to Attribute.1 and Attribute.2 columns. You may have to play around with this or other split/extract functions depending on the data you actually have. I can imagine your agencies may not all conform to a single rule.  3. Then you can rename all of the columns as you want - just double click on the column header. 4. When you're done, go back to the Home tab and click Close & Load To..., then select where you want to put the new table - probably a new worksheet. Click Load and you're done!
|
|
#
¿
Feb 2, 2022 21:07
|
|
|
Haven't had my caffeine yet this morning but that sounds like a job for a union/merge with a date table. Create another table that has all the dates for the month and add that to PQ, then do a merge from that table to your receipts table using the date fields and a Full Outer join. This will match & merge all of the dates from both tables, any dates with blank receipts will still show up. If it were me, I'd create a date table with the next year+ of dates that also has columns for fiscal year, fiscal month, and fiscal week. Then when you do your monthly analysis, just add a step before the merge mentioned above and filter the date table to month you're working on.
|
|
#
¿
Feb 10, 2022 17:15
|
|
|
Before pasting CSV data or otherwise importing, select the entire sheet and change the number formatting to Text?
|
|
#
¿
Mar 3, 2022 17:26
|
|
|
Oh god you're dealing with that sort of stuff. Yeah I would be using SSIS/SQL or even Access if I had to as Excel is not going to be friendly.
|
|
#
¿
Mar 3, 2022 17:43
|
|
|
Add the CSV file as a data source (Power Query > From File > From Text/CSV), ignore whatever happens in the first dialog and hit the Edit button to open up the PQ editor. Then on the far right-hand side, click the gear icon to the right of the Source step on this new query and play around with the Line Breaks and Delimiter options. You've kinda already done this so it may not work either. The Transform > Split Column > By Delimiter option may be useful too.
|
|
#
¿
Oct 14, 2022 03:43
|
|
|
A Real Happy Camper posted:I have a sheet that is being sent to a variety of people to fill out with inventory items, and I want to have a second sheet that can collect the data from all the copies of the first. A Real Happy Camper posted:It's ~30 different branches, with all different levels of ability, so I'll probably just set up a folder for my manager to use, and fill it with blank forms for the time being. I'm gonna pimp Power Query again as this is another great use case for it that doesn't require any coding to set up and would allow you an unlimited number of forms that are all compiled into one master file at the click of the 'refresh' button. The only requirement is that you all have access to SharePoint or OneDrive, including all of your branches, although technically a network shared folder would work too...
|
|
#
¿
Oct 26, 2022 04:09
|
|
|
Is the Book1 file already open when you run it and get that error?
|
|
#
¿
Nov 21, 2023 04:35
|
|
|
Apologies I'm a few years out of having done any VBA and also a few beers into a frickin'-mondays-dude evening but I think you do have to save the file if you're going to reference it like that. Otherwise I think you can just activate the specific sheet you need if the file is already open regardless of saved status and ultimately there is some sort of application goto code you could use that references both the file and sheet name. Based on your original description, I would recommend your process start with saving that exported data file to a location that's easily accessible like the Downloads file or Desktop or whatever. Then close the file (or don't, shouldn't matter), open your macro workbook, and click the button. You can either build the macro to always open the Book1.xlsx from the same folder location or you could have the macro do a file-open dialog and you pick the file, whatever floats your boat.
|
|
#
¿
Nov 21, 2023 05:43
|
|
|
Combine the date and time column pairs so you have a date-time data type (yyyy/mm/dd hh:mm:ss) then subtract the two new date-time cells. You shouldn't need a duration or datedif formula for this as Excel handles it natively. You may need to do some math to get it into just hours though. If you can't combine the columns then that requires more brain power than I'm able to provide right now.
|
|
#
¿
Nov 27, 2023 22:44
|
|
|
|
| # ¿ Apr 27, 2024 23:42 |
|
|
Mostly yes, with Power Query. The most relevant example I can find: https://learn.microsoft.com/en-us/power-query/connectors/web/web-by-example There are a ton of caveats with web scraping but in general it works. You can get it to happen automatically but by default you'll have to hit the 'Refresh All' button on the Data tab. It may be against the website's Terms of Service, there may be a rate limit, they may just tired of the web calls and block you, etc. But I wouldn't really be worried about any of that with your use case.
|
|
#
¿
Jan 25, 2024 00:50
|
|