Align data in three columns

[AAinSB]

Hi...thank you for reading! I am trying to align a handful of order datapoints sorted by zip code for the past three years--left five columns have 2010 info, middle 5 have 2011 and right 5 have YTD 2012. However, there are different counts for the years. Example--90210 may have five sales in 2010, 8 in 2011 and 6 in 2012. How do I make sure that the first record for Zip Code 90211 starts in the Row 9 i.e. for 2010, there are three blank rows after the five sales, 0 blanks for 2011 and 2 for 2012. The data is currently in three parts, one for each of the years.


I started by giving each record a unique ID e.g. 90210.01, 90210.02, etc. figuring that would help with any lookup functions, but then lost the plot.


DATA EXAMPLE

(There are three of these data sets--one for each year...can easily be combined into one, if that's helpful.)


ZIP Date Product Quantity Revenue

90210 9/1/12 A 10 $95

etc.


Thanks VERY much for any thoughts!


AA

 

[kaushik03]

Hi AA,


Welcome to this forum and we glad to have you here.


If your query is to know the position of the first occurrence of a ZIP code, then I think MATCH function should work right!


I am not sure about placing each data set(for each unique ZIP) in different columns, but , however, if I assume your data set spread across col A to Col E and from row 1 to row 27; and also you have 3 ZIPs in this data set. For e.g.; 90210 ranges from row1 to row 10; 90211 ranges from row 11 to row 19 and rest is for ZIP 90212 that starts from row 20.


So accrding to your QS you should get 1 for 90210, 11 for 90211 and 20 for 90212.


Now, at G1 we have kept any one one of the three ZIPs.


At G2 we write =MATCH($G$1,$A:$A,0).....this will give you position of the first occurrence of the ZIP in the data set.


If this is not something that you are looking for, please upload a sample workbook here:

http://chandoo.org/forums/topic/posting-a-sample-workbook


Kaushik

 

[NARAYANK991]

Hi AA ,


Will all the years have the same ZIPs , or will any ZIP be missing in one or more years ?


For example , will your data be as follows :

[pre]

90210	9/1/2012    A    10	95	90210	9/1/2012    A	10	95	90210	9/1/2012    A	10	95
90210	9/3/2012    B    31	101	90210	9/3/2012    B	31	101	90210	9/3/2012    B	31	101
90210	9/10/2012   A    22	92	90210	9/10/2012   A	22	92	90210	9/10/2012   A	22	92
90210	9/12/2012   D     7	120	90210	9/12/2012   D	7	120	90210	9/12/2012   D	7	120
90210	9/17/2012   C    19	77	90210	9/17/2012   C	19	77	90210	9/17/2012   C	19	77
90211	9/3/2012    A    31	101	90210	9/17/2012   A	31	101	90210	9/18/2012   A	31	101
90211	9/4/2012    D    22	92	90210	9/17/2012   D	22	92	90212	9/4/2012    D	22	92
90211	9/5/2012    C     7	120	90210	9/17/2012   C	7	120	90212	9/5/2012    C	7	120
90211	9/11/2012   B    19	77	90210	9/18/2012   B	19	77	90212	9/11/2012   B	19	77

[/pre]
Narayan

 

[Hui]

But you want it to end up like this ?

[pre]

90210	9/01/2011	A	10	95	90210	9/01/2012	A	10	95	90210	9/01/2013	A	10	95
90210	9/03/2011	B	31	101	90210	9/03/2012	B	31	101	90210	9/03/2013	B	31	101
90210	9/10/2011	A	22	92	90210	9/10/2012	A	22	92	90210	9/10/2013	A	22	92
90210	9/12/2011	D	7	120	90210	9/12/2012	D	7	120	90210	9/12/2013	D	7	120
90210	9/17/2011	C	19	77	90210	9/17/2012	C	19	77	90210	9/17/2013	C	19	77
90210	9/17/2012	A	31	101	90210	9/18/2013	A	31	101
90210	9/17/2012	D	22	92
90210	9/17/2012	C	7	120
90210	9/18/2012	B	19	77
90211	9/03/2011	A	31	101	90211	9/18/2012	a	12	15
90211	9/04/2011	D	22	92	90212	9/18/2012	a	12	15
90211	9/05/2011	C	7	120
90211	9/11/2011	B	19	77
90212	9/04/2013	D	22	92
90212	9/05/2013	C	7	120
90212	9/11/2013	B	19	77

[/pre]