Re-arranging rows to match column values.

**zacb** · 06-26-2013, 09:13 AM

Hey,

I have a large spreadsheet that is from 2 seperate reports, column's A-G is the first report and I-K is the second. In Column A I have a tenancy number, and B-G is the name/address/postcode. In I-K I have the tenancy number in Column I, but just the name and 1st line of address in J-K. A-G and I-K are both sorted by the tenancy number's in A and I in ascending order. At the moment in H I have a small sum subtracting Column A from Column I so I know if it's 0 they're a match as they share the same tenant number. I'm doing this manually atm, which is extremely time consuming when you have over 2000 of them - i'm currently inserting blank rows in I-K till they match then highlighting the whole row. (all the tenants on column I-K are in the report on A-G but not vice versa)

Is there a way to match these rows up automatically? So I have tenancy number and details in columns A-G matching up with the tenancy number and details in I-K.

Hopefully this makes sense!

Many Thanks.

**zacb** · 06-26-2013, 09:14 AM

Attachment 245852

Sorry, forgot to attach.

**Bhuvi** · 06-26-2013, 09:19 AM

Hi Zacb,
This attachment is not valid, please attach a valid attachment.

**zacb** · 06-26-2013, 09:25 AM

Hey,

Apologies. Hopefully this one will work -

Many Thanks.

**AlKey** · 06-26-2013, 10:02 AM

Use this:
=IF(H3=0,A3,"")

**zacb** · 06-26-2013, 11:12 AM

Thanks for your answer - appreciate it.

Problem is I haven't got 0 in all the sums on Column H as I'm doing that as I go along manually shifting rows in I-K. So I have about 1500 rows left.

That's the easiest way I've found to see if it matches the tenant number on Column A.If I do the sum and drag it down column H I can see the ones that already match (are 0) or match after some shifting.

Basically, atm I have

Tenant No | Name | Address Tenant No | Name | Address
1 KL 42 2 AL 23
2 AL 23 3 RF 25
3 RF 25 6 ED 21
5 AS 54 8 GW 29
6 ED 21 10 GL 43
7 ZS 34 12 SA 59
8 GW 29 19 EQ 92
10 GL 43 24 LO 64

and so on. At the moment I'm inserting blank rows on the second report, to push that first row on I-K down so it would match 2-3 on the first report leaving the 1st blank as it isn't on the 2nd report. I want it so the 2nd report which is I-K automatically matches the tenant no and details from A-G.