Need formula to parse data

djl0525

Well-known Member
Joined
Dec 11, 2004
Messages
1,240
Setup
Sheet1!H5:AA5 contains alphanumeric entries like X3, N2, E1, G4, H2, AL1, B1, etc. The alpha part of the entries is unique and can be one or two characters. The numeric part is always one digit (1-9).

Goal
I need to get the numbers that were entered in random order from Sheet1 to a specific order on Sheet2 (without the alphas).

Example 1
In Sheet2!H5, I need a formula that will look at Sheet1!$H5:$AA5 and if any cell in that range begins with the letter "G", return the number that comes after G, which is 4. If there is no G, return blank.

Example 2
In another cell, the formula will be edited to look for entries that begin with "AL", for example, and return the number after AL, which is 1. If there is no AL in the range, return blank.

Note: Each cell on Sheet2 will look for a different alpha on Sheet1 and return the value after the alpha.

Any assistance will be greatly appreciated!
-- DJ
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sample data table:

Excel Workbook
HIJKLMNOPQRSTUVWXYZAA
1LZ0B6OQ1Z0H2R2EO9J4T4FH2QT4I3NJ4MY9AV6X8JJ4R2SZ0BG1
2K5HG1MG1SF0F0X8O9Y9RV6G1Q1GV6P0GO9K5OI3MV6A5E9Q1
3Q1A5OX8GB6ES3CM7SA5I3L6R2ZU5R2Z0K5I3JS3UT4M7B6N8
4MJ4T4X8Z0KY9T4LH2SN8B6C7A5W7RC7G1V6CC7A5R2TB6N8
5I3V6A5ZP0I3RJ4RX8W7BP0A5LK5ND8Y9G1MW7Q1Q1VJ4WB6ZB6
Sheet1



Sample formula:

Excel Workbook
AB
1KY9
Sheet1


Looks at the cell in A1, finds any value that matches in H1:AA5, and figures out the number on the right. I assume that all KY would end in the same number, so that there isn't a case where you'd have KY3 and KY6 in the same data set.
 
Upvote 0
Wow. Thanks so much for your help! I don't know how you did it, but you did it! There's just one thing I failed to mention earlier. Some cells are blank in the range which results in the #VALUE! error.

Also, I made 2 changes to the formula:

  1. The data is on Sheet1, but the formulas are on Sheet2.
  2. The data range is H5:AA5, not H5:AA9.
The current array formula is:
=AVERAGE(IF(LEFT(Sheet1!$H5:$AA5,LEN(Sheet1!$H5:$AA5)-1)=H$1,--(RIGHT(Sheet1!$H5:$AA5,1))))

Can you edit it to allow blanks in H5:AA5?

Thanks again!
--DJ
 
Upvote 0
Okay, Data Set:
Excel Workbook
HIJKLMNOPQRSTUVWXYZAA
1Z0U5CI3Q1WE9CW7FR2SY9R2A5GM7G1Z0IZ0
2B6UA5Q1VU5AK5PW7T4H2XQ1NA5QR2K5TD8
3K5V6V6P0XS3ZI3M7SO9T4L6WT4LM7X8I3
4LO9TZ0FW7VS3LT4ML6X8P0Y9E9VY9L6
5L6YY9D8I3QJ4Z0KZ0F0G1Y9
6R2YZ0ON8SB6Y9G1BB6L6W7NQ1
7U5Z0S3FC7E9I3EJ4MI3T4A5
8BG1LV6N8R2FN8DG1YD8B6I3K5IC7AE9
9OY9ZZ0EK5N8FL6SA5QC7UI3HG1BN8Q1VO9H2
Sheet1


Here's the solution:
Excel Workbook
AB
1CI3
Sheet2
 
Upvote 0
Re: Need formula to make a list of data from a running real time data entries.

Hello MrExcel, i want you to help me now.
How we get an updated list data entries directly from a running and real time recording?
example:
Day 1st
Time
Name Buy

07.00 John 5 units
07.30 Smith 2 units
09.00 Alexa 3 units
10.15 John 2 units


Day 2nd
Time Name Buy

07.15 Smith 1 unit
07.20 John 10 units
09.00 Edward 6 units
10.15 John 7 units


ect....

Here I want to make a list moving data that record my customers directly and real time.
This is my goal.

07.15 Smith 1 units
07.20 John 10 units
09.00 Edward 6 units

10.15 John moves to 17 units automatically(data 07.20 - John removed) and continue to the day 2nd ect....

Thanks all..

icon14.gif
icon14.gif
icon14.gif
 
Upvote 0
Sal, it does not return blank if it does not find the letter(s) it's looking for. It returns #DIV/0!. Please advise.

=AVERAGE(IF(Sheet1!H5:AA5<>"",IF(LEFT(Sheet1!H5:AA5,LEN(Sheet1!H5:AA5)-1)=H$1,--(RIGHT(Sheet1!H5:AA5,1)))))

--DJ
 
Last edited:
Upvote 0
The error is gone, but the formula puts zeroes where there should be blanks. I'll live with it. I'll come up with a workaround or something.

Formula I'm using...
=IF(ISERROR(AVERAGE(IF(Sheet1!$H3:$AA3<>"",IF(LEFT(Sheet1!$H3:$AA3,LEN(Sheet1!$H3:$AA3)-1)=H$1,--(RIGHT(Sheet1!$H3:$AA3,1)))))),"",AVERAGE(IF(Sheet1!$H3:$AA3<>"",IF(LEFT(Sheet1!$H3:$AA3,LEN(Sheet1!$H3:$AA3)-1)=H$1,--(RIGHT(Sheet1!$H3:$AA3,1))))))

I really appreciate the time you spent on this. It will be a major time-saver for several people!!

Thanks again! -- DJ
 
Upvote 0
Nevermind what I said about getting zeroes. I discovered some of the formulas weren't arrays as they should have been. I fixed that and now it's working perfectly, just as you wrote it!

Thanks! -- DJ
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top