HLOOKUP and OFFSET - Any Suggestions
 

I have two sets of data, from which I am trying to extract values of one to
insert into the other. My problem is that for each value in my first set
there can be more than one value in the second set, and that the second set
of data can contain multiple rows.

Perhaps the following is a better illustration:

Data Set A:

A B C
D
Customer ID Type of Service #1 Type of Service #2 Type of Service #3
545
646
747
848

Data Set B

A B
Customer ID Type of Service
545 Oil Change
545 Tyres
646 Oil Change
646 Tyres
646 Filters
747 Oil Change
848 Tyres
848 Filters

I need to bring into my sheet for Data Set A all of the values for Service
Type from Data Set 2, with one row per Customer ID & multiple columns for
Service Type, one value per column. The problem is that the data in data set
two is arranged so that each Customer ID has multiple rows with one Service
Type per row.

I have some experience with HLOOKUP, although I am more familiar with
VLOOKUP, but it is offsetting the lookup that baffles me.

I feel sure that this is possible, but I just don't know where to start, and
as this is a problem that arises for me frequently it would be great to find
out once and for all how to resolve it.

Can anyone help?

Regards,

Dickie

HLOOKUP and OFFSET - Any Suggestions
 

If I understand correctly then you want to count the amount of oil changes
etc each customer has had. Try:

=SUM((Sheet2!A3:A10=545)*(Sheet2!C3:C10="Oil Change"))

This formula looks in the range A3:A10 on sheet 2 to find customer 545 and
C3:C10 to find out how many oil changes they had (1 in your sample date).

It's and array formula so enter with CTRL+SHIFT+Enter

Mike

"Dickie Worton" wrote:

I have two sets of data, from which I am trying to extract values of one to
insert into the other. My problem is that for each value in my first set
there can be more than one value in the second set, and that the second set
of data can contain multiple rows.

Perhaps the following is a better illustration:

Data Set A:

A B C
D
Customer ID Type of Service #1 Type of Service #2 Type of Service #3
545
646
747
848

Data Set B

A B
Customer ID Type of Service
545 Oil Change
545 Tyres
646 Oil Change
646 Tyres
646 Filters
747 Oil Change
848 Tyres
848 Filters

I need to bring into my sheet for Data Set A all of the values for Service
Type from Data Set 2, with one row per Customer ID & multiple columns for
Service Type, one value per column. The problem is that the data in data set
two is arranged so that each Customer ID has multiple rows with one Service
Type per row.

I have some experience with HLOOKUP, although I am more familiar with
VLOOKUP, but it is offsetting the lookup that baffles me.

I feel sure that this is possible, but I just don't know where to start, and
as this is a problem that arises for me frequently it would be great to find
out once and for all how to resolve it.

Can anyone help?

Regards,

Dickie

HLOOKUP and OFFSET - Any Suggestions
 

Mike,
Thanks very much for replying. Unfortunately, it isn't what I am trying to
achieve, although it is something else I didn't know how to do, so it isn't
wasted!

I think the key word I missed out of my original posting was 'transpose'.

What I actually want to do is to transpose the values of Service Type for
each Customer ID in Data Set B into Data Set A, so that there is only one row
per Customer ID and all of the values for Service Type are shown in columnns
B-D (in the example).

The example doesn't come close to the number of rows in my actual
spreadsheet (I have over 6000 rows in my 'real' Data Set A, so just using
Copy & Paste Special:Transpose doesn't seem to be an option as it would be
fairly labour intensive.

I would like to use something like a HLOOKUP to identify all of the rows
relating to a particular Customer ID in Data Set B & to then transpose values
into columns B-D of that particular Customer ID row in Data Set A.

Does that make sense?

Sorry if I'm having trouble making clear what I'm after.

"Mike H" wrote:

If I understand correctly then you want to count the amount of oil changes
etc each customer has had. Try:

=SUM((Sheet2!A3:A10=545)*(Sheet2!C3:C10="Oil Change"))

This formula looks in the range A3:A10 on sheet 2 to find customer 545 and
C3:C10 to find out how many oil changes they had (1 in your sample date).

It's and array formula so enter with CTRL+SHIFT+Enter

Mike

"Dickie Worton" wrote:

I have two sets of data, from which I am trying to extract values of one to
insert into the other. My problem is that for each value in my first set
there can be more than one value in the second set, and that the second set
of data can contain multiple rows.

Perhaps the following is a better illustration:

Data Set A:

A B C
D
Customer ID Type of Service #1 Type of Service #2 Type of Service #3
545
646
747
848

Data Set B

A B
Customer ID Type of Service
545 Oil Change
545 Tyres
646 Oil Change
646 Tyres
646 Filters
747 Oil Change
848 Tyres
848 Filters

I need to bring into my sheet for Data Set A all of the values for Service
Type from Data Set 2, with one row per Customer ID & multiple columns for
Service Type, one value per column. The problem is that the data in data set
two is arranged so that each Customer ID has multiple rows with one Service
Type per row.

I have some experience with HLOOKUP, although I am more familiar with
VLOOKUP, but it is offsetting the lookup that baffles me.

I feel sure that this is possible, but I just don't know where to start, and
as this is a problem that arises for me frequently it would be great to find
out once and for all how to resolve it.

Can anyone help?

Regards,

Dickie