Posts by slick225

The Code provided is interesting but I wouldn't know how to apply it to my spreadsheet, I was looking for an Excel formula or formulas to accomplish the task.
However your approach did give me an idea, using the spaces as separator makes the data a bit more manageable, but that require me to save the tag message as a text file, then import it into Excel. It's a viable workaround at the moment since time is of the essence, but if you ( alansidman) or anyone select have a formula approach I'd love to read it.
Note: [USER="108665"]AlanSidman[/USER] thank you for your quick response, I'm just no where near your level to deploy your provided code (one day my friend, I'll be there)

I'm in search of a formula that will extract specific content that will vary in length. I'm working with a file that tags, these tags come in the following variety: 8=, 49=, 150=, etc. and the values following these tags can vary in length. the tags have a specific meaning but the positioning of the tags may not always be the same.
I need a formula (not VB Code, as I'm not proficient in VB), that will allow me to locate a tag and display it's value. Note: after each value there is a space to separate the tags, so the formula needs to find a specific tag (example: 52=20190522) and report only 20190522 by displaying everything after the 52= and up until the next space.
Because the file has multiple tags, I need it to identify specific tags and report from that tags = character to the next space which ends the tag presented and starts the next tag.

Sample Data:
8=FIX.4.2 9=123 35=7 56=Testfile 52=20190522-12:35:56

Expected Results:
Cell A1 = FIX.4.2
Cell A2 = 123
Cell A3 = 7
Cell A4 = Testfile
Cell A5 = 20190522-12:35:56

Thank you in advance for any help

I'm needing to create a fixed position file, I want to create a spreadsheet that will make the creation of that file easier. As an example say the first field of the text file is held in positions 01-32, field 2 is position 33-40. If the value of field 1 is only 5 characters long I need a formula (not VBA code, as I'm not as familiar with VBA), that will take the value I enter and add spaces for the remaining positions, (Field #1 (01-32), my value "ABCDE" (01-05), then spaces in position 06-32.

What I'm trying to do is:
1. use CELL A2 to house my value "ABCDE"
2. a formula that will take "ABCDE" and add the remaining spaces

Is there a formula that I can enter the positions or actual number of spaces and the formula takes my entered value and replaces some of the spaces while still maintaining a position length of 32 characters.

Re: Filters or Arrays used to condense data to a smaller selection

Thank you Tom, this is the type of formula I used over a coouple years ago. "If you don't use it, well...you know".

Thanks again for all the help

Re: Filters or Arrays used to condense data to a smaller selection

Jindon, that worked perfectly.
Final Question, and I do really appreciate your help and knowledge.

Question: If I wanted to included a tally of the results how would you incorporate that into the code?
Example:
Cell B7 would be the lable cell (Number of Week Days), Cell C7 would give a count of everything returned for an entered Activity Code.

Re: Filters or Arrays used to condense data to a smaller selection

Jindon, thank you for the VB Code, it works perfectly. I'm new to VB so it's also a great learning opportunity for me as well.
I do have a couple of additional questions. In my actual data I have the data elements (the data table similar the the table that started in A2 in the original sample) located in one sheet (Sheet2 (My Data)) while the query and results (originally in the sample as Cell G6 and F8) should appear on a separate sheet (Sheet 1 (My Query)). Where in the code can I designate VB to pull the data from a different worksheet? I'm attaching a better representation of a sample.

Also are there any methods available from a formula perspective (INDEX, ARRAY, etc.) that could also accomplish this same task, VBA looks cleaner and I like the results, but I'd like to see if a Formula method is available. I'm leaning heavily to the VB method.

See attached file

I want to be able to Type in or select the Activity Code from a dropdown and be presented with a listing of the Wk Days that the activity occur and a listing of the activity/activity name using either arrays, filters, or some other formula that will accomplish the goal.
Pivot tables work but the layout is not how I'd like to present the data.

In my "Target Results" example (provided in the attachment), BC in cell G6 is what I'd type in or select from dropdown and the result would be a filtered listed of A2:C26, the end results would show only the week days and Activity (name) from the list based on the Activity code entered in G6.

NOTE: If the data (A2:C26) has to be made into a table or if it or the formula has to be moved to another sheet I'm ok with that

My primary data is a listing of application screens that impact other app screens, and some screens are impacted my multiple screens, my goal is to create a spreadsheet that will allow me to select a screen and see what screens are impacted.

See Sample Spreadsheet attached.

Thank you,

I've been given a spreadsheet which comes out of a time tracking application. The spreadsheet list projects and resources(people), and the time expected and acutally spent. Currently a lot of manual changes are being done once this report is generated. The report is always in the same format but because of the number of resources the rows specific data appears will vary from month to month.

Example:
Row 1 = Project Name
Row 2 = Resource Name 1 (this row also contains the hours for resource1)
Row 3 = Resource Name 2 (this row also contains the hours for resource2)
Row 4 = Totals (totals up the number of resource hours for each week)

My problem is that the values reported are in a Text Format. I've created a worksheet thats grabbing the data from the original report, but when I attempt to sum the totals for each week to get the totals for the month I get 0 because the values on the main worksheet are text, also I want the user to be able to paste in the next months report without having to convert manually (hoping I can do a conversion formula on my raw data worksheet). I'm using a lot of IF formulas to pull specific data from the report, and I've pulled over my project names and the totals per week, but now due to the conversion problem I can't sum for the month.

I've used a similar formula in the past but its for an opposite situation (number to text) =TEXT(A1,"0000000000"), is there a formula that converts it to numeric form? sorry I couldn't post an example.

Thanks in advance for any help.

Special Note: I hope the Title was accurately written for this post, tried to make it descriptive enough, thanks again for your help.

I'm trying to shorten a calcuation by using a single formula in one cell as oppose to my current method of multiple calculations over multiple cells.

Sample attached:
I use 3 hidden columns to display my Risk level then another formula to put them in one column.

The formula I'm trying to use generates a #VALUE! error and I can't figure out why.

New Formula:
=OR(IF(AND(A2=0,B2=0),"High Risk",""),IF(AND(A2=0,B2>=1),"Medium Risk",""),IF(AND(A2>=1,B2>=1),"Low Risk",""))

Re: Compiling a list of specific data from a single or multiple columns

ByTheCringe2,
Thanks, I haven't done much with VBA but this will be a good opportunity to learn. Please provide the formula and I'll give it a shot, or if you could provide the formula and the sample spreadsheet with the formula as a reference. Thanks.

I've been trying to create a formula that will take data with a specific status and put those in a list on a summary worksheet.

On my data worksheet I have two columns a category/name column and a risk/status column, on my summary page I want to generate a list solely made up of names that have a specific status.

I've attached a sample.

Thanks in advance for your help.

Re: Multiple option IF formula

To everyone that participated on this thread, thank you for all your brain power and hard work.

I just wanted to update everyone on my task.

I decided to go a completely different route: The goal was originally to report on our teams knowledge levels of products and based on how many Expert users, Well versed users, and no experienced users we had, to display weather it was a High, Medium, or Low risk area.

My approach: (using a combination of IF, OR, & ANDs)
I first did a count of all types, then using the counts did the following:
=IF(OR(And(scenario 1)),And(scenario 2), "High Risk", "")

This actually got me the exact results I was looking for, I was able to use various scenarios to decide my High, Medium, and Low risk categories.

Scenarios looked like this: A2=>3, B2=<4, etc.

By the way, my apologies for the "Can a" in the Tread Heading.

Re: Can a Multiple option IF formula be created?

ByTheCringe2 is see what you mean, in that sample using only three colors would work perfectly fine, my trouble is formulating a way to determine if a vaule has no 4, and 3 statuses its considered a Very High or High Risk.

Re: Can a Multiple option IF formula be created?

I haven't used VBA, or nested statements where can I find info on either?

I need a formula that will calculate if a Category is at Very High, High, Med, or Low level risk. I've attached a sample file for review. In the sample I use a countif and countblank formula to count the number of specific statuses used. On my summary page I want to use the counts to determine if a category is in one of the 4 risk levels. I've broken down additional information within the attached file.
Thanks in advanced for any help.

I have two spreadsheets one an updated version of the other. I need to compare to see the changes. Does excel have a feature that will allow you to view the differences similar to the Diff command in UNIX or the compare feature in Text Pad Pro?

Re: Trying to avoid quote and comma stripping in formulas

Thanks for the tip Batman, my file gen is coming along nicely thanks to your help.

I have to create a formated file to test our system every other week, I want to create a File Generator using Excel and the replacement values forumla "& A1 &". However the file is in a CSV format containing a lot of quotes and when I attempt to use a line of the file in a formula with "& <Cell> &" excel reports errors, if you accept excels help it strips out the quotes and commas. Anyway around this? having to have a user save the file is CSV format would be to complicated.

Sample file attached.

Re: Calculating dates using business days only

Analysis Toolpac was unchecked - Thanks its working now

Thanks for your help

Re: Calculating dates using business days only

When I attempt to use the =Workday formula I get the #NAME? error, I've also tried formatting the cell for date. Any Suggestions?