Transfer data based on 3 criterias

[shihyang]

Hi,

I have a spreadsheet that has 2 tables in it and looks like this:

Seller, Type, Oct, Nov, Dec
John, 1, 20, 30, 40
John, 2, 50, 60, 70

Seller, Type, Aug, Sept, Dec
Cara, 1, 10, 20, 30
Cara, 2, 40, 50, 60

I am trying to rearrange the data such that it looks like this:

Seller, Type, Aug, Sept, Oct, Nov, Dec
John, 1, 0, 0, 20, 30, 40
Jonh, 2, 0, 0, 50, 60, 70
Cara, 1, 10, 20, 0, 0, 30
Cara, 2, 40, 50, 0, 0, 60

Have tried sumif, treating the data as an array as well as the index and match function in excel but unable to do it.

The problem I am facing is that the header of both tables are not the same ie the months, therefore was wondering if there is another around it (without changing the headers of both tables).

Thanks
Desperate and having a headache.

 

[cornflakegirl]

Welcome to the board.

I would suggest that you convert both tables into the final format and then stick them together.

So for the first table, add in two columns to the left of the Oct column (use Insert - Shift to Right). Select the area and fill with zeros using Ctrl-Enter.

For the second table, add two columns to the left of Dec column in the same way.

They are now in the same format, and can be pasted one under the other.

If that isn't what you're after (and I suspect it may not be because of your "without changing the headers of both tables" comment), please give more details about exactly what you're looking for.

 

[shihyang]

Hi Cornflakegirl,

tks for the suggestion, but yeah that would be the easiest but I am unable to do that. I've currently got about 30+ tables with different headers and would be expecting more tables to come.

My final format is fixed so i'll have something that looks like this:

Seller, Type, Aug, Sept, Oct, Nov, Dec
John, 1
Jonh, 2
Cara, 1
Cara, 2

What i am trying to do is write a formula to pick up data from the first 2 tables and insert it into the relevant cells in the final format. The 3 criterias are:
If Seller in table 1 and 2 match Seller in final format,
If Type in table 1 and 2 match Type in final format,
If Months in table 1 and 2 match Months in final format,
Then copy data to final format in cell say John, 1, Aug.
So for this case, since there isn't any John, 1, Aug data, it should enter 0 into the final format table.

Hopefuly I've given enuf details .

 

[cornflakegirl]

shihyang - are you saying that you will want to do a lookup over 30+ tables? that's going to be really ugly - and probably too long to fit in the formula bar. Anything I suggest to allow the consolidation of two tables really isn't going to extend to that scenario.

Why do you have so many tables? Can you do anything about the way the source data arrives? If you could get it so you just had Seller, Type, Month fields (instead of a separate field for each month) there would be more tools (like pivot tables) available to you.

 

[shihyang]

well, till the day I get the authority to do that, ie change the format of the source....well i guees I am stuck with this format