Organizing Raw Data

[sd2173]

Good evening,

I have moderate VB experience but am completely new to Excel programming. I am developing a program which I expect to be fairly simple, however, I've not a clue where to start organizing my raw data. I figure my macro to be a fairly simple one, but am stuck.

My raw data will be in the following form (the "\" being my delimiter diving cells)

A B C D E
123456 \ Doe \ John \ XA \ 0.5
123456 \ Doe \ John \ XA \ 1.0
654321 \ Doe \ Jane \ SC \ 0.5
654321 \ Doe \ Jane \ SC \ 0.0

What I would like to do, is have a form where a button is clicked to perform the following function.

Search the new sheet, Sheet2, to see if an entry exists for my A column (e.g. if 123456 does not exist, create it, if it does exist move on to next function).

Then search column D, and according to value (e.g. MA, XA, etc.) add column E to the appropriate column in the existing or created row in Sheet2.

So, say given my mock raw data, I would like Sheet2 to have created two rows (123456 and 654321) and added all E column entries showing MA to one column, entries showing XA to another column, etc. So the end result is one row per # (e.g. 123456) and that row continually adds like entries Column E number.

The final Sheet2 product hoping to be:

A B (XA) C (SC)
123456 \ 1.5 \ 0
654321 \ 0 \ 0.5

Also if possible adding the Name data (columns B and C off Sheet1) in columns after the "adding data".

I know... Simple task right? It just seems like something I should be able to do in VB but working with Excel is new and given the huge amounts of entries I'm working with I would like to be able to run these totals with a click of a button before moving on.

Any guidance/advice is appreciated. Thank you in advance for taking the time to read/support.

Regards,
SD

 

[sd2173]

So, addition...

If doing in VB I feel it's as simple as placing the data into an arrry and seraching the array, performing functions, then creating a new one, adding totals etc. and outputting so it can be read by Excel, but I feel if I'm outputting to Excel anyway, I should be able to handle the functions via program.

 

[Marcelo Branco]

Hi,

If you only want a vba-code it will not help you (sorry...), but you can achieve the same result without code, just using Remove Duplicates and formulas.

Copy the entire column A form Sheet1 to Sheet2 A2, select the values copied and go to Data>Remove Duplicates.

You get a column with only one instance of each code.

Still in Sheet2 insert the headers and formulas as below

A B C D E
<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=64>Code</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>XA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>SC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>Name1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>Name2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>123456

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1,5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Doe</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>John</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>654321</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0,5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Doe</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Jane</TD></TR></TBODY></TABLE>

Formula in B2
=SUMPRODUCT(--(Sheet1!$A$1:$A$500=$A2),--(Sheet1!$D$1:$D$500=B$1), Sheet1!$E$1:$E$500)

Copy to C2 and down

Formula in D2
=VLOOKUP($A2,Plan1!$A$1:$C$500,2,FALSE)
copy down

Formula in E2
=VLOOKUP($A2,Plan1!$A$1:$C$500,3,FALSE)
copy down

HTH

M.

 

[sd2173]

Marcelo,

VB is not an issue so these functions are brilliant which I can place as macros. However, the second column of raw data (e.g. XA or SC) includes more than those 2 identifiers, there will be number listings (e.g. LL, LW, SA, SC, DC, DW, etc. etc,) and I want all those to go to the appropriate column, and as your function did, add the number.

When I just worked with identifiers (e.g. XA and SC) I gave in my "problem request" your solution worked fantastic, however, unfortunately I have many more options in that column which will need to be accounted for/ / organized. I think you have the solution I'm looking for and thought I may be able to decipher it from your formulas, but have been unable to and come up empty handed.

**Any future advice is apprecited, thank you so much.**

sd

 

[Marcelo Branco]

Hi sd,

If you have so many possibilities in the second column as you said, maybe a possible solution is to Remove Duplicates copying and selecting columns A and B.

Doing so you get unique values of the pair Code-Identifier

Then we can adjust the SumProduct formulas to add the correct rows (with the same identifier) to something like

=SUMPRODUCT(--(Sheet1!$A$1:$A$500=$A2),--(Sheet1!$D$1:$D$500=$B2), Sheet1!$E$1:$E$500)

NOT TESTED, i have to say

M.

 

[Marcelo Branco]

Reading again your post i noticed that you need each indentifier in a different column.

To get this i think that a solution is to copy column B to a column in Sheet2, Remove Duplicates and use the TRANSPOSE function to convert the unique values from this column to row 1 starting at column B.

And use the same formulas of my first post coying from B2 to each column with a Identifier.

M.