Return text values and numbers (SUMIF/ISNUMBER/COUNTIF?)

[Valj]

Hello everyone,

I am a daily user of Excel and have been scouting the forum for months, it has always been of great help to me and I am very grateful for all of your sound advice.

However I have recently got to scratch my head facing a formula problem, I know solving it is a matter of combining several basic formulas together but I cannot get my head around it as I have been thinking about it for too long I guess.

I want to return the 3 sets of letters AND the number associated to it. This source data is an extract from a pivot table I cannot change. So lets say in Column B we have the following:

AAA
121212
34534
456456

BBB
456789
4645
34534

I would like to automatically get in Column A by dragging down a formula ONCE for the whole column A:

AAA121212
AAA34534
AAA456456

BBB456789
BBB4645
BBB34534

Please note that same numbers can be present under any letter sets.

It would be awesome if someone could shed the light on this problem!

Thanks

Valj

[FlameRetired]

Hi Valj. Welcome to the forum.

Maybe this?

	A	B	C
1		AAA
2	AAA121212	121212	In A2: =IF(ISNUMBER(B2),LOOKUP("zzzzz",$B$1:B1)&B2,"")
3	AAA34534	34534
4	AAA456456	456456
5
6		BBB
7	BBB456789	456789
8	BBB4645	4645
9	BBB34534	34534

[Valj]

Hi FlameRetired,


Thanks for the prompt reply. I was thinking something along your suggestion but yours works straight away!

What does the "zzzzz" stand for? Just a bit confused here

Thanks

Val

[FlameRetired]

LOOKUP performs a 'look-back' in the increasing range $B$1:B1. It looks for the last text less than or equal to "zzzzz". The "zzzzz" is sort of a "standard" overkill ... a

text string most likely to always be greater than the last text in $B$1:B1.

In this case is starts by returning all AAA. When it encounters a different string ... BBB in this case ... it returns that until it encounters another string.

Did that help?

[Valj]

Yes it does, I was not aware such a feature was possible with LOOKUP, thanks a million for your time/explanation!

The formula works great in my example but it happens that in my real set of data, values in Column B are not always numbers and can be text/general values containing letters now and then. What would be a quick fix to replace ISNUMBER?

Thanks

Val

[FlameRetired]

That sound challenging. I would have to see this in context.

If you are not aware of it you can upload a sample Excel workbook directly to the forum.

Here's how:

To attach a file to your post,

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[Valj]

Attached is a more thorough example very close to reality. I am trying to find a solution to it but help is always welcome

Thanks,

Val

[Valj]

Would it be wise to try and use nested IFS successively testing for all possible number and text values in cell B2? (There are more than 40 possible numbers and texts)
I have nested a huge IF formula with all values in column B as per below making sure IF functions are grouped by 6 with "&" inbetween:

IF(B2=3808932143,LOOKUP("zzzzz",$B$1:B1)&J6, IF(B2="3808A00314",LOOKUP("zzzzz",$B$1:B1)&B2.....,""))

I cannot make it to work though, it does not return what I want.

Help anyone?

Val

[johnmpl]

Hi, to both!

You could try:
=IF(ISERR(--LEFT(B2)),"",LOOKUP(2,1/ISERR(--LEFT(B$1:B1)),B$1:B1)&B2)

Check file. Blessings!

[Valj]

Hi Johnmpl,

Well this is a definite killer, thanks a lot. This is the Excel level I wish to have, one day.

Blessings indeed,

Thanks,

Valj

[FlameRetired]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.