line up cells

[QuietRiot]

I have a workbook with 2 groups of data on it side by side. Goal here is to have them line up. The problem is one side has more data then the other OR the smaller side may have data thats not in the larger side.

example:

Accnt #, Name, address Acct#, Name, address

I would like:

1234 Mike Thomas 1234 Mike Thomas
1234 Jayne Thomas 1234 Jayne Thomas
1235 Chris Beaton
1236 Susan Hamel 1236 Susan Hamel


any help

 

[Boller]

Are there 3 columns or 2 columns in each group?
What are the column refs?

Can just one column in each group be used to match the groups?
If so what column?

 

[QuietRiot]

3 columns (actually more but if you can get 3 i'll tweak the code). There really is 10 on the left, 5 on the right

but account #, address are what I can use and should use to do compares

columns E and N = account # and is the only thing that would be suitable for matching. the address column is like 90% off by certain characters etc so I use a fuzzy match to validate. There is also same account numbers different names/addresses.

what i need is just to have the account #'s line up left/right

 

[Boller]

Post a copy of your worksheet. Need to see the layout

 

[QuietRiot]

I'll show you what i want in the next thread but this is a rough copy of what i have. only the real deal is like 24,000 rows. I left all this at work, im at home now so i just did it from memory.

*edit

PERSONAL.XLS
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Fund	Account	NewAcct	Name	Address	ISN	Confirm	Statement			Account	Name	Address	Confirm	Statement
2	1097	22234	22234	*name	*add	2343	Y	N			22234	*name	*add	Y	N
3	1134	1114525	22234	*name	*add	2343	Y	N			123123	*name	*add	Y	N
4	2222	1123123	123123	*name	*add	2343	Y	N			123333	*name	*add	Y	N
5	1107	1123333	123333	*name	*add	2343	Y	N			123333	*name	*add	Y	N
6	1097	1134134	134134	*name	*add	2343	Y	N			134134	*name	*add	Y	N
7	1142	2223441	223441	*name	*add	2343	Y	N			223441	*name	*add	Y	N
8	1111	1231234	223441	*name	*add	2343	Y	N			313414	*name	*add	Y	N
9	1097	2234241	223441	*name	*add	2343	Y	N			323424	*name	*add	Y	N
10	1123	33313414	313414	*name	*add	2343	Y	N			334143	*name	*add	Y	N
11	1097	2323424	323424	*name	*add	2343	Y	N			335454	*name	*add	Y	N
12	1207	22334143	334143	*name	*add	2343	Y	N			4414334	*name	*add	Y	N
13	1097	335454	335454	*name	*add	2343	Y	N			4414334	*name	*add	Y	N
14	1097	4414334	414334	*name	*add	2343	Y	N
Sheet1

 

[QuietRiot]

the main issue is that I have duplicate accounts so i want it lined up like this.

PERSONAL.XLS
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Fund	Account	New Acct	Name	Address	ISN	Confirm	Statement			Account	Name	Address	Confirm	Statement
2	1097	22234	22234	*name	*add	2343	Y	N			22234	*name	*add	Y	N
3	1134	1114525	22234	*name	*add	2343	Y	N
4	2222	1123123	123123	*name	*add	2343	Y	N			123123	*name	*add	Y	N
5	1107	1123333	123333	*name	*add	2343	Y	N			123333	*name	*add	Y	N
6											123333	*name	*add	Y	N
7	1097	1134134	134134	*name	*add	2343	Y	N			134134	*name	*add	Y	N
8	1142	2223441	223441	*name	*add	2343	Y	N			223441	*name	*add	Y	N
9	1111	1231234	223441	*name	*add	2343	Y	N
10	1097	2234241	223441	*name	*add	2343	Y	N
11	1123	33313414	313414	*name	*add	2343	Y	N			313414	*name	*add	Y	N
12	1097	2323424	323424	*name	*add	2343	Y	N			323424	*name	*add	Y	N
13	1207	22334143	334143	*name	*add	2343	Y	N			334143	*name	*add	Y	N
14	1097	335454	335454	*name	*add	2343	Y	N			335454	*name	*add	Y	N
15	1097	4414334	414334	*name	*add	2343	Y	N			4414334	*name	*add	Y	N
16											4414334	*name	*add	Y	N
Sheet1

 

[QuietRiot]

i think the best way of doing this would be to count the number of times the account is on one side and compare to the number of times its on the other side.

look down account column on left side.. account is there 3 times look on account column right side.. account is there 2 times then Range("bla blarightside").Insert Shift:=xlDown if account is there 4 times then Range("bla blaleft side").insert shift:=xlDown

 

[Boller]

I notice that the account numbers in the second group are sometimes old numbers and sometimes new numbers.

This makes it a bit complicated if the account number + the name is to be used for matching.

Is there not a column other than the account number that can be used for matching the two groups?

Do you really need the groups lined-up side by side?
There might be easier ways of identifying duplicate records by combining the lists into one list and then identifying the duplicates via a few formulas in adjacent columns.

i think the best way of doing this would be to count the number of times the account is on one side and compare to the number of times its on the other side.

look down account column on left side.. account is there 3 times look on account column right side.. account is there 2 times then Range("bla blarightside").Insert Shift:=xlDown if account is there 4 times then Range("bla blaleft side").insert shift:=xlDown

It's not quite as easy as you seem to think.

 

[QuietRiot]

new account and Account are matching account #'s

the other account on the left side is not needed since the new account was taken from that by grabbing the Right 6 digits

 

[Boller]

The following doesn't do exactly what you requested, but it might be helpful as a start from which further manipulation can be done if required.

Dim rng As Range, area As Range
Application.ScreenUpdating = False
[N:N].Insert
Range([K2], [K65536].End(xlUp)).Resize(, 6).Cut [C65536].End(xlUp)(2)
Set rng = Range([C2], [C65536].End(xlUp)).Offset(, -2)
rng.EntireRow.Sort Key1:=[C2], Order1:=xlAscending
For Each area In rng.SpecialCells(xlCellTypeBlanks).Offset(, 2)
    area.Resize(, 8).Insert Shift:=xlToRight
Next
[N:N].Delete
Application.ScreenUpdating = False