Sorting data

[Brookevcl]

Hi! I have a huge list (60,000) of contact data including information I want and a lot I don’t need. It is unevenly spread across cells in a way that isn’t patterned throughout (like some of the data is in correct columns but some is not). I basically want to delete the excess data and sort the rest. An example end product I would want is:
(Name, Address, City, State, Zip)
but because it’s all jumbled, I have to hand sort it. Also, I need to delete out all the extra data like account data and business names. Is there an easier way to do this through code? Or do I just need to hand do it all? Any help would be greatly appreciated!

 

[hagia_sofia]

It would be helpful if you could post a sample of the data so that it is possible to answer your question...

 

[Brookevcl]

It would be helpful if you could post a sample of the data so that it is possible to answer your question...

Here is a sample of the data and what I need to sort it into

 

[hagia_sofia]

Address, state, city, and zip code could probably be extracted with a combination of REGEX and INDEX functions, name seems to be more complicated; I guess you are not allowed to share the whole dataset or some larger sample?

 

[Brookevcl]

Address, state, city, and zip code could probably be extracted with a combination of REGEX and INDEX functions, name seems to be more complicated; I guess you are not allowed to share the whole dataset or some larger sample?

How would I go about using those functions? I am not allowed to share a larger sample, but it basically goes on exactly like that for 60,000 rows

 

[hagia_sofia]

Please test this, insert it in the ADDRESS column of the output table and drag it down (adjust range so that the last column that contains data is selected):

=LET(
array,TOROW(TRIM(A1:O1),1),
string,FILTER(array,IF(ISNUMBER(SEARCH("* ?? *",array)),ISNUMBER(--(RIGHT(array,1))))=TRUE),
address,INDEX(array,,XMATCH(string,array,0)-1),
state,TEXTBEFORE(TEXTAFTER(string," ",-2)," "),
city,TEXTBEFORE(string," ",-2),
zip,TEXTAFTER(string," ",-1),
HSTACK(address,state,city,zip))

Then check where there are some problems, return here and describe them/post another sample with those problematic rows.

As for the NAME column, could you please post another 5-10 rows of the data?

 

[Brookevcl]

Please test this, insert it in the ADDRESS column of the output table and drag it down (adjust range so that the last column that contains data is selected):

=LET(
array,TOROW(TRIM(A1:O1),1),
string,FILTER(array,IF(ISNUMBER(SEARCH("* ?? *",array)),ISNUMBER(--(RIGHT(array,1))))=TRUE),
address,INDEX(array,,XMATCH(string,array,0)-1),
state,TEXTBEFORE(TEXTAFTER(string," ",-2)," "),
city,TEXTBEFORE(string," ",-2),
zip,TEXTAFTER(string," ",-1),
HSTACK(address,state,city,zip))

Then check where there are some problems, return here and describe them/post another sample with those problematic rows.

As for the NAME column, could you please post another 5-10 rows of the data?

This is so helpful! Thank you so much. As for the problem areas, it looks as though they are occurring in areas where the data is misplaced in the input. I will link a photo example, the top being a correct input and output and the bottom being incorrect.

 

[hagia_sofia]

This is not due to misplaced input but due to the fact the SEARCH part of the formula returns two strings that match the pattern instead of one. You could try to address this particular problem by changing 1 to 2 in this part of the formula:

(RIGHT(array,1)

whenever there is a problematic row.

Or you could test this:

=LET(
a,
LET(
array,TOROW(TRIM(A1:O1),1),
string,FILTER(array,IF(ISNUMBER(SEARCH("* ?? *",array)),ISNUMBER(--(RIGHT(array,1))))=TRUE),
address,INDEX(array,,XMATCH(string,array,0)-1),
state,TEXTBEFORE(TEXTAFTER(string," ",-2)," "),
city,TEXTBEFORE(string," ",-2),
zip,TEXTAFTER(string," ",-1),
HSTACK(address,state,city,zip)),
IF(COLUMNS(a)>4,
LET(
array,TOROW(TRIM(A1:O1),1),
string,FILTER(array,IF(ISNUMBER(SEARCH("* ?? *",array)),ISNUMBER(--(RIGHT(array,2))))=TRUE),
address,INDEX(array,,XMATCH(string,array,0)-1),
state,TEXTBEFORE(TEXTAFTER(string," ",-2)," "),
city,TEXTBEFORE(string," ",-2),
zip,TEXTAFTER(string," ",-1),
HSTACK(address,state,city,zip));a))

I am not sure it will address all the problems - could you post how many of 60,000 rows are still problematic after this "patch"?

 

[Brookevcl]

This is not due to misplaced input but due to the fact the SEARCH part of the formula returns two strings that match the pattern instead of one. You could try to address this particular problem by changing 1 to 2 in this part of the formula:

(RIGHT(array,1)

whenever there is a problematic row.

Or you could test this:

=LET(
a,
LET(
array,TOROW(TRIM(A1:O1),1),
string,FILTER(array,IF(ISNUMBER(SEARCH("* ?? *",array)),ISNUMBER(--(RIGHT(array,1))))=TRUE),
address,INDEX(array,,XMATCH(string,array,0)-1),
state,TEXTBEFORE(TEXTAFTER(string," ",-2)," "),
city,TEXTBEFORE(string," ",-2),
zip,TEXTAFTER(string," ",-1),
HSTACK(address,state,city,zip)),
IF(COLUMNS(a)>4,
LET(
array,TOROW(TRIM(A1:O1),1),
string,FILTER(array,IF(ISNUMBER(SEARCH("* ?? *",array)),ISNUMBER(--(RIGHT(array,2))))=TRUE),
address,INDEX(array,,XMATCH(string,array,0)-1),
state,TEXTBEFORE(TEXTAFTER(string," ",-2)," "),
city,TEXTBEFORE(string," ",-2),
zip,TEXTAFTER(string," ",-1),
HSTACK(address,state,city,zip));a))

I am not sure it will address all the problems - could you post how many of 60,000 rows are still problematic after this "patch"?

This works but because there are so many problematic rows, it would take forever to individually input. Is there a way to sort out a keyword? Like only have all rows with more than the two letters in the state column be shown? That way I could possibly use the formula for all of them in a chunk rather than having to sort it all myself? Thank you so much again!

 

[hagia_sofia]

The new "endless" formula is supposed to be dragged down, i.e. you can use on all rows at once (i.e. even on those that are fine) and see the result (no need to change 1 to 2 manually). To identify problematic rows you could indeed e.g. create a helper column and use =LEN(A1) where A1 represents the STATE column; if you drag it down you can then filter rows which are > 2 because it should probably contain only two.