EXCEL DATA ANALYSIS

4 Topics

Extracting substrings from brackets
Hi, I'm working with a large dataset based on journal entry character strings. I have standardized names coded into the strings to allow easier reference for an analysis. An example below (not real entries): Column A I went to the store with Joe [Joe Green] today. I played cards with Abby [Abby Miller] and the Joe-man [Joe Green]. etc... I would like to extract each substring between brackets and concatenate those substrings (separated by commas) in the next column. Ideally, I could do this task in a single formula. Hence, the output would appear: Column B (desired) Joe Green Abby Miller, Joe Green etc... I have tried several other approaches and none produced the intended output. I formulated my best attempt as follows (the roster has a list of standardized, bracketed names; the raw_notebook_data has the journal entries with standardized, bracketed names embedded in the strings): =IF(SUMPRODUCT(--ISNUMBER(( SEARCH(roster!$B$2:$B$178, INDEX(raw_notebook_data!$A$1:$M$100000, MATCH($A2,raw_notebook_data!$A$1:$A$100000,0), MATCH(K$1,raw_notebook_data!$A$1:$M$1,0))))))>0, MID(INDEX(raw_notebook_data!$A$1:$M$100000, MATCH($A2,raw_notebook_data!$A$1:$A$100000,0), MATCH(K$1,raw_notebook_data!$A$1:$M$1,0)), SEARCH([",INDEX(raw_notebook_data!$A$1:$M$100000, MATCH($A2,raw_notebook_data!$A$1:$A$100000,0), MATCH(K$1,raw_notebook_data!$A$1:$M$1,0)))+1, SEARCH("]",INDEX(raw_notebook_data!$A$1:$M$100000, MATCH($A2,raw_notebook_data!$A$1:$A$100000,0), MATCH(K$1,raw_notebook_data!$A$1:$M$1,0)))- SEARCH("[",INDEX(raw_notebook_data!$A$1:$M$100000, MATCH($A2,raw_notebook_data!$A$1:$A$100000,0), MATCH(K$1,raw_notebook_data!$A$1:$M$1,0)))-1), "NA") The formula above produces output as follows (only the first bracketed substring appears): Column B (current) [Joe Green] [Abby Miller] etc... This post is my first post on this forum, so I hope I provided the necessary information to receive some help. Please let me know if you need additional information.
Solved
aawve
Nov 12, 2019 Place Excel
1.3KViews
0likes
3Comments
Overwhelmed trying to resolve this.
Hi, I have a list of counties and the assigned rep for that county. I then have a fill rate report by county. I am trying to get a formula that will compare the counties then pull the name of the assigned rep into a column on the fill rate report by county. Its late, I've overthought this. Now I am hoping someone sees a simple answer to this. Thanks!
Cyclonewars
Nov 05, 2019 Place Excel
993Views
0likes
1Comment
Logical Functions:
Can someone help me with writing the following function: If X is =or > than 4.6 give it a value of 4 + if X is > 4.23 but <=to 4.60 give it a value of 3 + if X >3.85 but <= to 4.23 give it a value of 2 + if X < 3.85 give it a value of 1 Thanks
weareit12
Nov 03, 2019 Place Excel
1.2KViews
0likes
2Comments
DATA ANALYSIS HISTOGRAM FUNCTION 'MACRO'
I am doing a repetitive task use of the Data Analysis Histogram function on #4 sheets within a workbook - I am seeking is it possible for an automating of the process - which produces a Histogram Result from a bin Range of a Colum of Data. In each case, I need to press the Data Analysis, select histogram, enter the Input Range, Bin Range and Output Range (selecting a cell) each data piece of which are the same for all #4 sheets. On each sheet I open the above enter the data and press 'OK'. I step through each sheet and this takes about 2-3 mins, but would be great if its possible to write a Macro or press one button. Any ideas would be amazing. Thank you.............
PETEMAGS
Oct 24, 2019 Place Excel
836Views
0likes
0Comments