Need formula to parse data

[djl0525]

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

 

[Sal Paradise]

Sample data table:

Excel Workbook
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	LZ0	B6	OQ1	Z0	H2	R2	EO9	J4	T4	FH2	QT4	I3	NJ4	MY9	AV6	X8	JJ4	R2	SZ0	BG1
2	K5	HG1	MG1	SF0	F0	X8	O9	Y9	RV6	G1	Q1	GV6	P0	GO9	K5	OI3	MV6	A5	E9	Q1
3	Q1	A5	OX8	GB6	ES3	CM7	SA5	I3	L6	R2	ZU5	R2	Z0	K5	I3	JS3	UT4	M7	B6	N8
4	MJ4	T4	X8	Z0	KY9	T4	LH2	SN8	B6	C7	A5	W7	RC7	G1	V6	CC7	A5	R2	TB6	N8
5	I3	V6	A5	ZP0	I3	RJ4	RX8	W7	BP0	A5	LK5	ND8	Y9	G1	MW7	Q1	Q1	VJ4	WB6	ZB6
Sheet1

Sample formula:

Excel Workbook
	A	B
1	KY	9
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.

 

[djl0525]

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

 

[Sal Paradise]

Okay, Data Set:

Excel Workbook
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Z0	U5		CI3		Q1	WE9	CW7	FR2	SY9	R2				A5	GM7	G1	Z0		IZ0
2	B6		UA5	Q1		VU5				AK5	PW7	T4		H2		XQ1	NA5	QR2	K5	TD8
3	K5	V6		V6	P0		XS3	ZI3	M7	SO9	T4		L6	WT4		LM7	X8		I3
4	LO9	TZ0	FW7				VS3	LT4	ML6		X8	P0	Y9					E9	VY9	L6
5				L6			YY9	D8		I3	QJ4			Z0		KZ0		F0	G1	Y9
6		R2	YZ0	ON8			SB6	Y9	G1			BB6		L6	W7			NQ1
7		U5	Z0	S3		FC7			E9	I3		EJ4		MI3	T4		A5
8	BG1	LV6		N8	R2			FN8	DG1	YD8	B6	I3				K5		IC7		AE9
9	OY9	ZZ0		EK5		N8	FL6	SA5	QC7	UI3		HG1				BN8	Q1	VO9		H2
Sheet1

Here's the solution:

Excel Workbook
	A	B
1	CI	3
Sheet2

 

[faistone]

ADVERTISEMENT

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..

 

[djl0525]

http://www.mrexcel.com/forum/member.php?u=113228Faistone, you must create a new thread when you ask for help. Click the New Thread button on the Excel Questions page and delete your post from this thread. And someone will help you.

Good luck -- DJ

 

[djl0525]

ADVERTISEMENT

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

 

[Sal Paradise]

Excel Workbook
	A	B
1	ZY
Sheet2

This works.

 

[djl0525]

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

 

[djl0525]

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