Comparing row data to column data

[mikegoyra]

Hello,

I' really lost and unsure how to proceed. I think I need to use VBA to accomplish this, but I am open to suggestions. I have a complicated situation. I have 2 sheets in two different workbooks and the data in each sheet is arranged differently. On sheet 1, I have this:

HotelID Tax Name Percentage Flat Tax Tax Authority ID Category Business Model
34 City Tax .024 0 TUpdate1213 1 Merchant
34 Lodging .028 0 TUpdate1213 1 Dual
35 Occupancy .019 1 TUpdate1213 0 Merchant
34 City Tax .014 0 TUpdate1213 1 Merchant
119 Tourism .010 1 TUpdate1213 1 Dual
....and so on....

This sheet has a couple of hundred thousand of these.

The problem is that the second sheet (in another workbook) is arranged differently. There is only one line per ID and all of the various taxes are listed as separate columns, so it looks like this:

CityTax Lodging Occupancy Tourism VAT SalesTax HotelTax .....(about 50 of these)
34 .024 .028 .019 .010 - .10 .03 .........

Each HotelID has just one line with all of the taxes listed as individual columns. I need some sort of formula/script that can match the ID's and transpose all the corresponding taxes (1 column over for the actual value) into the appropriate columns.

Does anyone have any method to accomplish this?

 

[Momentman]

transpose all the corresponding taxes (1 column over for the actual value) into the appropriate columns.

Can you paste a sample of the final output, preferably as tables

 

[Valley]

Hello,

I' really lost and unsure how to proceed. I think I need to use VBA to accomplish this, but I am open to suggestions. I have a complicated situation. I have 2 sheets in two different workbooks and the data in each sheet is arranged differently. On sheet 1, I have this:

HotelID Tax Name Percentage Flat Tax Tax Authority ID Category Business Model
34 City Tax .024 0 TUpdate1213 1 Merchant
34 Lodging .028 0 TUpdate1213 1 Dual
35 Occupancy .019 1 TUpdate1213 0 Merchant
34 City Tax .014 0 TUpdate1213 1 Merchant
119 Tourism .010 1 TUpdate1213 1 Dual
....and so on....

This sheet has a couple of hundred thousand of these.

The problem is that the second sheet (in another workbook) is arranged differently. There is only one line per ID and all of the various taxes are listed as separate columns, so it looks like this:

CityTax Lodging Occupancy Tourism VAT SalesTax HotelTax .....(about 50 of these)
34 .024 .028 .019 .010 - .10 .03 .........

Each HotelID has just one line with all of the taxes listed as individual columns. I need some sort of formula/script that can match the ID's and transpose all the corresponding taxes (1 column over for the actual value) into the appropriate columns.

Does anyone have any method to accomplish this?

you can copy and then use paste special, and select transpose, finally you will get (column2row or row2column) what you want.