Attempting to use INDEX function for the first time

[Von Pookie]

I've got 2 worksheets with company names/addresses and the codes associated with them.

They are set up as follows:

Book1
	A	B	C	D
1	Code	CompanyName/Address	Billto
2	AMT2	Dummyco. 100ABCSt. Here,OH12345	Same
3	MOI2	TestProducts 5026thSt. There,IL12345	Same
4	BWP6	Made-upInc. 9003KingSt. Everywhere,NY12345	Dummyco. 100ABCSt. Here,OH12345
Sheet1

One worksheet is domestic companies, the other international.

I'm attempting to make a separate index page from the other 2 sheets, but instead of typing everything out, I figured I'd try this.

What I want to get is just the code, company name (no address), company location (no address or zip), and whatever is under bill to.

I've got the initial index function to work, however, I'm stuck on how to get just the company name or city/state-city/country from the one cell. Or even if I can, for that matter.

Any ideas? Or should I just start entering it all "by hand?"

Thanks,

 

[phantom1975]

This formula worked for me:

=LEFT(A1,FIND("
",A1)-1)

In the FIND portion, it is " then ALT+ENTER then " again.

 

[Von Pookie]

But how can I implement that in the index function?

I think what may be confusing me is that I'm not using a specific cell address like you did with the LEFT function. The formula I'm using to populate the cells is =INDEX(Domestic!$A$4:$C$49,1,2,1)

I'm just plain lost, apparently

 

[phantom1975]

I can't get HTML Maker to work on this one, but here's the formula I used to create the data you need:

=A1& " " & LEFT(B1,FIND("
",B1)-1) & " " & LEFT(RIGHT(B1,LEN(B1)-FIND("
",B1,FIND("
",B1)+1)),LEN(RIGHT(B1,LEN(B1)-FIND("
",B1,FIND("
",B1)+1)))-7) & " " & LEFT(C1,FIND("
",C1)-1)

In the FIND functions, the "" is actually " ALT+ENTER "

 

[Von Pookie]

Totally, completely and utterly lost.

!#%*&@$!

And I couldn't get your formula to work, either.

I'm going to take a little break from this for like...5 minutes, then come back and kick the machine some more because I'm too stupid to figure this out. :x

 

[Aladin Akyurek]

Totally, completely and utterly lost.

!#%*&@$!

And I couldn't get your formula to work, either.

I'm going to take a little break from this for like...5 minutes, then come back and kick the machine some more because I'm too stupid to figure this out. :x

Kristy,

I'm lost too. I can't still figure out why you use INDEX. That formula cannot be dragged. It just includes some constants to fetch a value. Are you looking for something like this...

Book6
	A	B	C	D	E	F	G	H	I
1	Code	CompanyName/Address	Billto				Company	Location
2	AMT2	Dummyco. 100ABCSt. Here,OH12345	Same		MOI2	2	TestProducts	There
3	MOI2	TestProducts 5026thSt. There,IL12345	Same
4	BWP6	Made-upInc. 9003KingSt. Everywhere,NY12345	Dummyco. 100ABCSt. Here,OH12345
Sheet1

The formulas are...

F2:

=MATCH(E2,$A$2:$A$5,0)

G2:

=IF(ISNUMBER(F2),WMID(INDEX($B$2:$B$4,F2),1,1,CHAR(10)),"")

H2:

=IF(ISNUMBER(F2),WMID(WMID(INDEX($B$2:$B$4,F2),3,1,CHAR(10)),1,1,","),"")

Aladin

PS. WMID requires the morefunc.xll add-in.

 

[Von Pookie]

Kristy,

I'm lost too. I can't still figure out why you use INDEX.

Because I rarely, if ever, do anything "advanced" like this, and don't know any better. I'm a big formula dummy. Basic usage, check. Some very, very rudimentary knowledge of VBA, check. But no one thing

But I'll give yours a go

 

[NateO]

Aladin's looks much cleaner, but since I started this debacle, I'm postin' it! Below you will find a very good example of how not to approach this (too many lookups).

Book3
	A	B	C	D	E
1	FullFormulae
2	Code	FullBlock	Line1	Line2	Line3
3	AMT2	Dummyco. 100ABCSt. Here,OH12345	Dummyco.	100ABCSt.	Here,OH
4	MOI2	TestProducts 5026thSt. There,IL12345	TestProducts	5026thSt.	There,IL
5
6	LookUps
7	Code		Co.	Address	Location
8	AMT2		Dummyco.	100ABCSt.	Here,OH
9	MOI2		TestProducts	5026thSt.	There,IL
Sheet2

All native, no Longre assistance required (which is not inherently good). Oh, and no #n/a trap either... Like I said, not good.

 

[Von Pookie]

Aladin? I'm still a little confused. What is the MATCH function doing and why?

I understand that the other formulas are based off of this, but the page I want to make is starting out entirely blank. I don't have a "lookup value" to go off of. It looks like you've got the right idea of what I want to do, though.

This has already been more trouble than it's worth, but on the other hand, it doesn't hurt to try new stuff

Edit: Nate, you snuck in there on me. You have to remember, though: your "not good" is WAY better than anything I could even dream up.

 

[Aladin Akyurek]

Aladin? I'm still a little confused. What is the MATCH function doing and why?

I understand that the other formulas are based off of this, but the page I want to make is starting out entirely blank. I don't have a "lookup value" to go off of. It looks like you've got the right idea of what I want to do, though.

This has already been more trouble than it's worth, but on the other hand, it doesn't hurt to try new stuff

I assume that you have the addresses along with codes somewhere. Just list (with copy/paste if there are no duplicates) in the destination area and use the codes as lookup values (as my exhibit shows) to rip off the addresses you bring over with INDEX/MATCH combination. MATCH computes the row (position) of a given code in the source. INDEX picks up the address at the same position from the address range as the code it's associated with. The foregoing assumes that the source and the destination areas will be in different sheets. If not, you can apply the WMID formulas directly to the address cells (No need for INDEX/MATCH). Too much 'Greek'?